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
ALTER TABLE ADD CONSTRAINT...
?