How do I add a column to a primary key?

[heather]

Hi!

Can I add a column to a primary key, or will I have to drop the current primary key and add a new one?

[NorbertKrupa]

You will have to drop and recreate it. Also remember that all columns participating in a primary key must be NOT NULL and will be converted if they are not:

Code: Select all

dbadmin=> CREATE TABLE public.test (
   a int PRIMARY KEY,
   b int NULL
);

dbadmin=> ALTER TABLE public.test ADD PRIMARY KEY (a, b);
WARNING 2623:  Column "b" definition changed to NOT NULL
ROLLBACK 4413:  Primary constraint for relation "test" already exists

dbadmin=> ALTER TABLE public.test DROP CONSTRAINT C_PRIMARY;

dbadmin=> ALTER TABLE public.test ADD PRIMARY KEY (a, b);
WARNING 2623:  Column "b" definition changed to NOT NULL

You can find the constraint name to drop in v_catalog.table_constraints (default is C_PRIMARY).

[heather]

Ok, that's what I figured. Thank you for writing back. Oh, and good to know about the not null constraint added