Postgres FK引用复合PK [英] Postgres FK referencing composite PK

问题描述

考虑

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

如何在 bar中创建引用 foo中的PK的FK？

How do I create an FK in "bar" that references the PK in "foo"?

推荐答案

如何在 bar中创建引用 foo中PK的FK？ / p>

How do I create an FK in "bar" that references the PK in "foo"?

使用您当前的结构，您将无法这样做。

With your current structure, you can't.

目标外键引用必须声明为PRIMARY KEY或UNIQUE。因此，这

The target of a foreign key reference has to be declared either PRIMARY KEY or UNIQUE. So either this

CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    foo_created_on ABSTIME,
    foo_deactivated_on ABSTIME,
    UNIQUE (id, foo_created_on)
);

或此

CREATE TABLE foo (
    id SERIAL,
    foo_created_on ABSTIME,
    foo_deactivated_on ABSTIME,
    PRIMARY KEY (id, foo_created_on),
    UNIQUE (id)
);

将作为bar.foo_id的目标。

would work as a target for bar.foo_id. Then bar would have a simple reference.

CREATE TABLE bar (
    id SERIAL,
    bar_created_on ABSTIME,
    bar_deactivated_on ABSTIME,
    foo_id REFERENCES foo (id),
    PRIMARY KEY (id, bar_created_on)
);

如果要引用最初在foo中声明的主键，则必须存储该主键在酒吧。您必须存储所有内容，而不是其中的一部分。因此，无需修改foo，您可以像这样构建bar。

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.

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

这篇关于Postgres FK引用复合PK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！