postgres constraint on one table according to data in another table-CodePudding

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?

CodePudding user response：

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);