postgresql Postgres FK 引用复合 PK
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9984022/
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
Postgres FK referencing composite PK
提问by punkish
Consider
考虑
CREATE TABLE foo (
id SERIAL,
foo_created_on ABSTIME,
foo_deactivated_on ABSTIME,
PRIMARY KEY (id, foo_created_on)
);
CREATE TABLE bar (
id SERIAL,
bar_created_on ABSTIME,
bar_deactivated_on ABSTIME,
foo_id REFERENCES ( .. what goes here? ..),
PRIMARY KEY (id, bar_created_on)
);
How do I create an FK in "bar" that references the PK in "foo"?
如何在“bar”中创建一个 FK 来引用“foo”中的 PK？
回答by Mike Sherrill 'Cat Recall'
How do I create an FK in "bar" that references the PK in "foo"?
如何在“bar”中创建一个 FK 来引用“foo”中的 PK？
With your current structure, you can't.
以你目前的结构，你不能。
The target of a foreign key reference has to be declared either PRIMARY KEY or UNIQUE. So either this
外键引用的目标必须声明为 PRIMARY KEY 或 UNIQUE。所以无论是这个
CREATE TABLE foo (
id SERIAL PRIMARY KEY,
foo_created_on ABSTIME,
foo_deactivated_on ABSTIME,
UNIQUE (id, foo_created_on)
);
or this
或这个
CREATE TABLE foo (
id SERIAL,
foo_created_on ABSTIME,
foo_deactivated_on ABSTIME,
PRIMARY KEY (id, foo_created_on),
UNIQUE (id)
);
would work as a target for bar.foo_id. Then bar would have a simple reference.
将作为 bar.foo_id 的目标。那么 bar 会有一个简单的参考。
CREATE TABLE bar (
id SERIAL,
bar_created_on ABSTIME,
bar_deactivated_on ABSTIME,
foo_id REFERENCES foo (id),
PRIMARY KEY (id, bar_created_on)
);
If you want to reference the primary key you originally declared in foo, you have to store that primary key in bar. You have to store all of it, not part of it. So without modifying foo, you could build bar like this.
如果要引用最初在 foo 中声明的主键，则必须将该主键存储在 bar 中。您必须存储所有这些，而不是其中的一部分。因此，无需修改 foo，您就可以像这样构建 bar。
CREATE TABLE bar (
id SERIAL,
bar_created_on ABSTIME,
bar_deactivated_on ABSTIME,
foo_id INTEGER NOT NULL,
foo_created_on ABSTIME NOT NULL,
FOREIGN KEY (foo_id, foo_created_on) REFERENCES foo (id, foo_created_on),
PRIMARY KEY (id, bar_created_on)
);
回答by Dave Halter
You have to create separate foreign keys:
您必须创建单独的外键：
CREATE TABLE bar (
id SERIAL,
bar_created_on ABSTIME,
bar_deactivated_on ABSTIME,
foo_id INT,
FOREIGN KEY (foo_id, created_on) REFERENCES foo (id, created_on),
PRIMARY KEY (id, bar_created_on)
);
