postgresql postgresql中的双外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9267535/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Double Foreign key in postgresql
提问by David Chan
I am trying to use a double primary key as a foreign key.
我正在尝试使用双主键作为外键。
Create table AAA (
AAA_id int primary key
);
create table BBB (
AAA_id int,
BBB_name character varying(20),
primary key (AAA_id, BBB_name)
);
create table CCC (
AAA_id,
BBB_name,
DDD_id,
... ???
);
table AAA is an object
表 AAA 是一个对象
table BBB is many to one with AAA, and holds aliases of AAA
表 BBB 与 AAA 多对一，并保存 AAA 的别名
I am trying to create a pivot table, CCC which holds a many to one between DDD and BBB.
我正在尝试创建一个数据透视表 CCC，它在 DDD 和 BBB 之间保持多对一。
I guess I want something like
我想我想要类似的东西
create table CCC (
AAA_id,
BBB_name,
DDD_id,
foreign key (AAA_id, BBB_name) references BBB(AAA_id, BBB_name) on update cascade
);
where both AAA_id and BBB_name are foreign keys, but they are also always referring to the same row in BBB.
其中 AAA_id 和 BBB_name 都是外键，但它们也总是指 BBB 中的同一行。
but of course that's not valid. what is the best way to produce this type of behavior in postgreSQL?
但这当然是无效的。在 postgreSQL 中产生这种行为的最佳方法是什么？
回答by Mike Sherrill 'Cat Recall'
Create temp table AAA (
AAA_id int primary key
);
create temp table BBB (
AAA_id int not null references AAA (AAA_id),
BBB_name character varying(20) not null,
primary key (AAA_id, BBB_name)
);
create temp table CCC (
AAA_id int not null,
BBB_name character varying(20) not null,
DDD_id integer not null,
-- Guessing at the primary key.
primary key (AAA_id, BBB_name, DDD_id),
foreign key (AAA_id, BBB_name) references BBB (AAA_id, BBB_name)
on update cascade
);
Since {AAA_id, BBB_name} uniquely identify a row in BBB, the foreign key {AAA_id, BBB_name} in CCC will also reference one unique row in BBB.
由于 {AAA_id, BBB_name} 唯一标识了 BBB 中的一行，因此 CCC 中的外键 {AAA_id, BBB_name} 也将引用 BBB 中唯一的一行。
