Обсуждение: Primary key on existing table?

This may be a silly question but I don't see any place where the
documentation explicitly addresses whether or not you can designate a
particular column as a primary key after the table is created.  I used
the "create table as " syntax to create a table with the same columns
as an original table, but I want to rename id column, make it a
serial, and designate it as a primary key.  Is it possible to do
this?  I've tried a number of things to do this and none of them have
worked.

Carol

Carol Walter <walterc@indiana.edu> wrote:
> This may be a silly question but I don't see any place where the
> documentation explicitly addresses whether or not you can designate
> a  particular column as a primary key after the table is created.

It sounds like you need ALTER TABLE DROP CONSTRAINT and ALTER TABLE
ADD table_constraint:

http://www.postgresql.org/docs/8.4/interactive/sql-altertable.html

-Kevin

On Tue, Aug 25, 2009 at 2:25 PM, Carol Walter<walterc@indiana.edu> wrote:
> This may be a silly question but I don't see any place where the
> documentation explicitly addresses whether or not you can designate a
> particular column as a primary key after the table is created.  I used the
> "create table as " syntax to create a table with the same columns as an
> original table, but I want to rename id column, make it a serial, and
> designate it as a primary key.  Is it possible to do this?  I've tried a
> number of things to do this and none of them have worked.

This is an alter.  Look up alter table, and look for constraints.
Standard format would look like this:

create table xyz (i int, t text);
ALTER TABLE / ADD PRIMARY KEY will create implicit index "xyz_pk" for
table "xyz"
ALTER TABLE
smarlowe=> \d xyz
      Table "public.xyz"
 Column |  Type   | Modifiers
--------+---------+-----------
 i      | integer | not null
 t      | text    |
Indexes:
    "xyz_pk" PRIMARY KEY, btree (i)

Carol Walter wrote:
> This may be a silly question but I don't see any place where the
> documentation explicitly addresses whether or not you can designate a
> particular column as a primary key after the table is created.  I used
> the "create table as " syntax to create a table with the same columns as
> an original table, but I want to rename id column, make it a serial, and
> designate it as a primary key.  Is it possible to do this?  I've tried a
> number of things to do this and none of them have worked.
>
> Carol
>

Is this close to what you are wanting to do ?

******* Create Test Table

test_it=# create table rename_me (
test_it(# id int primary key,
test_it(# name char(2)
test_it(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"rename_me_pkey" for table "rename_me"
CREATE TABLE

******* Table Status

test_it=# \d rename_me
      Table "public.rename_me"
  Column |     Type     | Modifiers
--------+--------------+-----------
  id          | integer      | not null
  name | character(2) |
Indexes:
     "rename_me_pkey" PRIMARY KEY, btree (id)

******* Drop Constraint (primary key index)

test_it=# alter table rename_me drop constraint rename_me_pkey;
ALTER TABLE

******* New Table Status

test_it=# \d rename_me
      Table "public.rename_me"
  Column |     Type     | Modifiers
--------+--------------+-----------
  id         | integer      | not null
  name   | character(2) |

******* Drop Constraint (not null)

test_it=# alter table rename_me alter column id drop not null;
ALTER TABLE

******* Table Status

test_it=# \d rename_me
      Table "public.rename_me"
  Column |     Type     | Modifiers
--------+--------------+-----------
  id         | integer      |
  name   | character(2) |

******* Rename Column (from id to new_id)

test_it=# alter table rename_me rename column id to new_id;
ALTER TABLE

******* Table Status

test_it=# \d rename_me
      Table "public.rename_me"
  Column |     Type     | Modifiers
--------+--------------+-----------
  new_id | integer      |
  name   | character(2) |

******* Change new_id from integer to serial
******* (this is from the Postgresql manual)

test_it=# CREATE SEQUENCE rename_me_new_id_seq;
CREATE SEQUENCE
test_it=# SELECT setval( 'rename_me_new_id_seq', ( SELECT MAX( new_id )
FROM rename_me ) );
  setval
--------

(1 row)

test_it=# ALTER TABLE rename_me ALTER COLUMN new_id SET DEFAULT nextval(
'rename_me_new_id_seq');
ALTER TABLE

test_it=# \d rename_me
                               Table "public.rename_me"
  Column |     Type     |                         Modifiers

--------+--------------+------------------------------------------------------------
  new_id | integer      | not null default
nextval('rename_me_new_id_seq'::regclass)
  name   | character(2) |


******* Add Primary Key

test_it=# alter table rename_me add primary key (new_id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"rename_me_pkey" for table "rename_me"
ALTER TABLE

******* Table Status

test_it=# \d rename_me
      Table "public.rename_me"
  Column |     Type     | Modifiers
--------+--------------+-----------
  new_id | integer      | not null default
nextval('rename_me_new_id_seq'::regclass)
  name   | character(2) |
Indexes:
     "rename_me_pkey" PRIMARY KEY, btree (new_id)

******* Done

Thanks, guys.  Worked like a charm.

Carol
On Aug 25, 2009, at 6:25 PM, Scott Marlowe wrote:

> On Tue, Aug 25, 2009 at 2:25 PM, Carol Walter<walterc@indiana.edu>
> wrote:
>> This may be a silly question but I don't see any place where the
>> documentation explicitly addresses whether or not you can designate a
>> particular column as a primary key after the table is created.  I
>> used the
>> "create table as " syntax to create a table with the same columns
>> as an
>> original table, but I want to rename id column, make it a serial, and
>> designate it as a primary key.  Is it possible to do this?  I've
>> tried a
>> number of things to do this and none of them have worked.
>
> This is an alter.  Look up alter table, and look for constraints.
> Standard format would look like this:
>
> create table xyz (i int, t text);
> ALTER TABLE / ADD PRIMARY KEY will create implicit index "xyz_pk" for
> table "xyz"
> ALTER TABLE
> smarlowe=> \d xyz
>      Table "public.xyz"
> Column |  Type   | Modifiers
> --------+---------+-----------
> i      | integer | not null
> t      | text    |
> Indexes:
>    "xyz_pk" PRIMARY KEY, btree (i)
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin