Обсуждение: rename primary key
Hi, Is it possible to rename a primary key? I learnt that to rename foreign keys one has to drop it and recreate with the new name, or add a new new and drop the old one. What is the approach for primary keys? Thanks. Cheers, -- Seb
2010/1/15 Seb <spluque@gmail.com>:
> Hi,
>
> Is it possible to rename a primary key? I learnt that to rename foreign
> keys one has to drop it and recreate with the new name, or add a new new
> and drop the old one. What is the approach for primary keys? Thanks.
Is this what you mean?
psql (8.4.2)
You are now connected to database "test".
test=# CREATE TABLE foo (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
serial column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
test=# \d foo Table "public.foo"Column | Type | Modifiers
--------+---------+--------------------------------------------------id | integer | not null default
nextval('foo_id_seq'::regclass)
Indexes: "foo_pkey" PRIMARY KEY, btree (id)
test=# ALTER INDEX foo_pkey RENAME to bar_fkey;
ALTER INDEX
test=# \d foo Table "public.foo"Column | Type | Modifiers
--------+---------+--------------------------------------------------id | integer | not null default
nextval('foo_id_seq'::regclass)
Indexes: "bar_fkey" PRIMARY KEY, btree (id)
test=#
Ian Barwick
On Fri, 15 Jan 2010 12:34:15 +0900, Ian Barwick <barwick@gmail.com> wrote: [...] > Is this what you mean? [...] Exactly! Thanks. On a related note: how come pgadmin3 shows "Indexes (0)" for such a table, even though an index does exist for the primary key? Are these indexes created in a separate table that is looked up by the foo table? -- Seb
Le 15/01/2010 04:39, Seb a écrit : > On Fri, 15 Jan 2010 12:34:15 +0900, > Ian Barwick <barwick@gmail.com> wrote: > [...] > On a related note: how come pgadmin3 shows "Indexes (0)" for such a > table, even though an index does exist for the primary key? Are these > indexes created in a separate table that is looked up by the foo table? > Primary keys are constraints. They are enforced with an index, but actually they are constraints. So we put them on the constraints nodes. There is the same behaviour for unique constraints. -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
On Fri, 15 Jan 2010 07:35:17 +0100, Guillaume Lelarge <guillaume@lelarge.info> wrote: [...] > Primary keys are constraints. They are enforced with an index, but > actually they are constraints. So we put them on the constraints > nodes. > There is the same behaviour for unique constraints. Thanks. Would there be any problem with listing the index used to enforce the primary key constraint in the Indexes node to let us know of its existence? In fact, psql does report it with the meta-command \d. It was somewhat confusing to see Indexes(0), and then having to rename an index to rename a primary key constraint. -- Seb
Le 15/01/2010 17:13, Seb a écrit : > On Fri, 15 Jan 2010 07:35:17 +0100, > Guillaume Lelarge <guillaume@lelarge.info> wrote: > > [...] > >> Primary keys are constraints. They are enforced with an index, but >> actually they are constraints. So we put them on the constraints >> nodes. > >> There is the same behaviour for unique constraints. > > Thanks. Would there be any problem with listing the index used to > enforce the primary key constraint in the Indexes node to let us know of > its existence? In fact, psql does report it with the meta-command \d. > It was somewhat confusing to see Indexes(0), and then having to rename > an index to rename a primary key constraint. > Sorry, forgot to answer this mail. It was asked a few times before, and AFAICT the answer is still the same. It is a constraint before being an index. The fact that it is also an index is an implementation detail. I understand this is confusing. -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com