1) Verified the two tables relations and check the data on those table.
If you required take table dump backup of these two tables.
test=# create table table_primary(id int primary key,name text);
NOTICE: CREATE TABLE / PRIMARY KEY
test=# create table table_foreign(id int references table_primary(id),location varchar);
CREATE TABLE
test=# \d table_primary
Table "public.table_primary"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
name | text |
Indexes:
"table_primary_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)
test=# \d table_foreign
Table "public.table_foreign"
Column | Type | Modifiers
----------+-------------------+-----------
id | integer |
location | character varying |
Foreign-key constraints:
"table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)
test=# insert into table_primary values (1,'RC');
INSERT 0 1
test=# insert into table_primary values (2,'RK');
INSERT 0 1
test=# insert into table_primary values (3,'Balu');
INSERT 0 1
test=# insert into table_primary values (4,'mahi');
INSERT 0 1
test=# insert into table_foreign values (1,'HYD');
INSERT 0 1
test=# insert into table_foreign values (2,'PUNE');
INSERT 0 1
test=# insert into table_foreign values (3,'PUNE');
INSERT 0 1
test=# insert into table_foreign values (4,'PUNE');
INSERT 0 1
test=# select * from table_primary ;
id | name
----+------
1 | RC
2 | RK
3 | Balu
4 | mahi
(4 rows)
test=# select * from table_foreign ;
id | location
----+----------
1 | HYD
2 | PUNE
3 | PUNE
4 | PUNE
2) Add new unique index before deleting existing primary key constraint,once it is done queries will start using new index.
test=# create unique index CONCURRENTLY unique_table_primary_id on table_primary(id);
CREATE INDEX
test=# \d public.table_primary
Table "public.table_primary"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
name | text |
Indexes:
"table_primary_pkey" PRIMARY KEY, btree (id)
"unique_table_primary_id" UNIQUE, btree (id)
Referenced by:
TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)
3) Drop existing primary key constraint,it will also dependent foreign key constraints also.
And then add the new primary key constraint and foreign key constraints those tables.
test=# begin ;
BEGIN
test=# alter table table_primary drop constraint table_primary_pkey CASCADE; alter table table_foreign add FOREIGN KEY (id) REFERENCES table_primary(id);
NOTICE: drop cascades to constraint table_foreign_id_fkey on table table_foreign
ALTER TABLE
ALTER TABLE
test=# commit ;
COMMIT
5) verify and make sure that you have the table definitions and contents of data in table,what you have before the operation.
test=# \d table_primary
Table "public.table_primary"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
name | text |
Indexes:
"unique_table_primary_id" UNIQUE, btree (id)
Referenced by:
TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)
test=# \d public.table_foreign
Table "public.table_foreign"
Column | Type | Modifiers
----------+-------------------+-----------
id | integer |
location | character varying |
Foreign-key constraints:
"table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)
test=# select * from table_primary ;
id | name
----+------
1 | RC
2 | RK
3 | Balu
4 | mahi
(4 rows)
test=# select * from table_foreign ;
id | location
----+----------
1 | HYD
2 | PUNE
3 | PUNE
4 | PUNE
(4 rows)
6) Now verify the tables primary and foreign key rules are working or not?
test=# insert into table_foreign values (10,'id');
ERROR: insert or update on table "table_foreign" violates foreign key constraint "table_foreign_id_fkey"
DETAIL: Key (id)=(10) is not present in table "table_primary".
test=# insert into table_primary values (1,'RC');
ERROR: duplicate key value violates unique constraint "unique_table_primary_id"
DETAIL: Key (id)=(1) already exists.
test=# insert into table_primary values (null,'RC');
ERROR: null value in column "id" violates not-null constraint
7) How to Add new column to existing composite Primary key :
i) master table is having composite PK with two columns.
postgres=# \d+ master1
Table "public.master1"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
name | text | not null | extended | |
created_date | date | | plain | |
loc | text | | extended | |
Indexes:
"master1_pkey" PRIMARY KEY, btree (id, name)
Child tables: child100,
child200
Has OIDs: no
2) drop existing Pk and add new index as PK.
postgres=# Begin;
create unique index CONCURRENTLY on master1 (id,name,created_date);
alter table table_primary drop constraint table_primary_pkey CASCADE;
alter table table_primary drop constraint table_primary_pkey CASCADE;
alter table master1 add CONSTRAINT PK_my_table PRIMARY KEY USING INDEX master1_id_name_created_date_idx;
commit;
NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "master1_id_name_created_date_idx" to "pk_my_table"
ALTER TABLE
4) Verify the Table
postgres=# \d+ master1
Table "public.master1"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
name | text | not null | extended | |
created_date | date | not null | plain | |
loc | text | | extended | |
Indexes:
"pk_my_table" PRIMARY KEY, btree (id, name, created_date)
Child tables: child100,
child200
Has OIDs: no
Hey, thanks for the article, really useful!
ReplyDeleteI think there is one missed action in the script for step #3:
ALTER TABLE primary ADD PRIMARY KEY USING INDEX unique_table_primary_id;
Another thing that we can do here is renaming the index before using it, like this:
ALTER INDEX unique_table_primary_id RENAME TO table_primary_pkey;