postgres约束一个表根据另一个表中的数据

Question

我有一个数据库，有2个表，foo和foo_trash。

都具有相同的结构，具有一个id(主键)和一个title。Foo_trash使用以下语句填充从foo复制的数据:

INSERT INTO foo_trash (SELECT * FROM foo WHERE id = 253)

我想在表foo_trash上添加一个constraint，这样如果在foo中不存在相同的pair id和title，则不会将任何行插入foo_trash。

我怎么写呢?

Answer 1

给定表foo:

create table foo (
id int,
title varchar(50),
primary key (id, title)
);

定义表foo_trash以引用您提到的两列:

create table foo_trash (
id int primary key,
title varchar(50),
FOREIGN KEY (id, title) REFERENCES foo (id, title)
);

现在你可以插入数据到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');

如果你试图在foo中不存在的foo_trash中插入一行，你将收到一个错误:

insert into foo_trash values (4, 'title4');

输出:

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".

可以在foo_trash中插入存在于foo:

中的行

insert into foo_trash values (3, 'title3');

你可以在foo_trash中插入成功的select from foo，假设id存在:

INSERT INTO foo_trash (SELECT * FROM foo WHERE id = 253);