I have a database with 2 tables, foo and foo_trash.
Both have the same structure with an id (primary key), and a title. foo_trash gets populated with data copied from foo with a statement like this:
INSERT INTO foo_trash (SELECT * FROM foo WHERE id = 253)
I would like to add a constraint on the table foo_trash so that no rows may be inserted into foo_trash if the same pair id and title is not present in foo.
How do I write that?
Given the table foo:
create table foo (
id int,
title varchar(50),
primary key (id, title)
);
Define the table foo_trash to reference the two columns you mentioned:
create table foo_trash (
id int primary key,
title varchar(50),
FOREIGN KEY (id, title) REFERENCES foo (id, title)
);
Now you can insert data into foo:
insert into foo values (1, 'title1');
insert into foo values (2, 'title2');
insert into foo values (3, 'title3');
insert into foo values (253, 'title253');
If you try to insert a row into foo_trash that doesn't exist in foo, you will receive an error:
insert into foo_trash values (4, 'title4');
Output:
ERROR: insert or update on table "foo_trash" violates foreign key constraint "foo_trash_id_title_fkey"
DETAIL: Key (id, title)=(4, title4) is not present in table "foo".
You can insert a row in foo_trash that exists in foo:
insert into foo_trash values (3, 'title3');
And you can do your insert into foo_trash as select from foo successfully, assuming that id exists:
INSERT INTO foo_trash (SELECT * FROM foo WHERE id = 253);
foo_id, I did say to add a constraint.