PostgreSQL – How to Duplicate Constraints Between Tables with Identical Structure

constraintpostgresqlpostgresql-9.4

I'm using PostgreSQL. I have two tables, table_a and table_b. table_b is a structural copy of table_a but it was created without the constraints of table_a. At a later date, I want to add the constraints to table_b.

All the constraints are regular foreign keys to the primary key of table_a and for the purposes of this question, we can assume that the data are the same in both tables, but I still don't want to drop and recreate table_b; I want to add the constraints independent of table creation.

Is there a generic way of doing this without having to be provided details of the tables' structure or the specific constraints of table_a?

Best Answer

CREATE TABLE table_m
(
    id int primary key,
    f1 int not null,
    f2 int not null,
    f3 int not null unique,
    unique (f1, f2)
);

CREATE TABLE table_a
(
    id int primary key,
    f1 int not null,
    f2 int not null,
    f3 int not null,
    constraint fk_f1_f2 foreign key (f1,f2) references table_m (f1,f2),
    constraint fk_f3    foreign key (f3)    references table_m (f3)
);

CREATE TABLE table_b
(
    id int primary key,
    f1 int not null,
    f2 int not null,
    f3 int not null
)

You can use information_schema view to get information about FOREIGN KEYS in this way:

SELECT   tc.constraint_name, 
         tc.table_name, 
         ccu.table_name AS foreign_table_name,
         string_agg(distinct kcu.column_name, ',') AS column_name,
         string_agg(distinct ccu.column_name, ',') AS foreign_column_name 
FROM     information_schema.table_constraints AS tc 
JOIN     information_schema.key_column_usage AS kcu
ON       tc.constraint_name = kcu.constraint_name
JOIN     information_schema.constraint_column_usage AS ccu
ON       ccu.constraint_name = tc.constraint_name
WHERE    constraint_type = 'FOREIGN KEY' 
AND      tc.table_name='table_a'
GROUP BY tc.constraint_name, tc.table_name, ccu.table_name;

constraint_name | table_name | foreign_table_name | column_name | foreign_column_name
:-------------- | :--------- | :----------------- | :---------- | :------------------
fk_f1_f2        | table_a    | table_m            | f1,f2       | f1,f2              
fk_f3           | table_a    | table_m            | f3          | f3

You can dynamically build SQL statements to add FOREIGN KEYS.

Copy&Paste and EXECUTE or use a function like this SO answer to execute.

SELECT   format('ALTER TABLE table_b ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES table_m(%s);',
                 tc.constraint_name, 
                 string_agg(distinct kcu.column_name, ','),
                 string_agg(distinct ccu.column_name, ','))
FROM     information_schema.table_constraints AS tc 
JOIN     information_schema.key_column_usage AS kcu
ON       tc.constraint_name = kcu.constraint_name
JOIN     information_schema.constraint_column_usage AS ccu
ON       ccu.constraint_name = tc.constraint_name
WHERE    constraint_type = 'FOREIGN KEY' 
AND      tc.table_name='table_a'
GROUP BY tc.constraint_name, tc.table_name, ccu.table_name;

| format                                                                                     |
| :----------------------------------------------------------------------------------------- |
| ALTER TABLE table_b ADD CONSTRAINT fk_f1_f2 FOREIGN KEY (f1,f2) REFERENCES table_m(f1,f2); |
| ALTER TABLE table_b ADD CONSTRAINT fk_f3 FOREIGN KEY (f3) REFERENCES table_m(f3);          |

dbfiddle here

Related Question