2

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?

2
  • Cannot run ALTER TABLE ADD CONSTRAINT...?
    – McNets
    Feb 19, 2018 at 13:52
  • There are somewhere on the order of 30 constraints, and I'm hoping for a way to do this agnostically, without regard to what the actual constraints are, so I can easily do it with another set of similarly paired tables. Feb 19, 2018 at 13:54

2 Answers 2

2
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

0

Use pg_dump with the "-s" (schema only) and "-t" (which table) options. That should give you a script in which you can alter the name of the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.