SQL 引用复合主键

Referencing a composite primary key

提问by Daniel o Keeffe

I have two tables, with each table having a composite primary key.

我有两个表，每个表都有一个复合主键。

One attribute is in both composite primary keys.

一个属性在两个复合主键中。

How am i supposed to reference the common attribute?? Do i just reference it as a FK in both tables as below? The cust_id and flight_id below are each part of the composite key as well and reference primary keys in other tables. (Ignore the third attribute in the erd for the br_flight table as I choose to use a composite key in the end).

我应该如何引用公共属性？我是否只是在下面的两个表中将其作为 FK 引用？下面的 cust_id 和 flight_id 也是组合键的每个部分，并引用其他表中的主键。（忽略 br_flight 表的 erd 中的第三个属性，因为我最终选择使用复合键）。

CREATE TABLE BOOKING_REFERENCE (
REFERENCE_ID NVARCHAR(10) NOT NULL,
CUST_ID NUMBER(10)NOT NULL,
STATUS NVARCHAR (1), NOT NULL,
PRIMARY KEY(REFERENCE_ID, CUST_ID),
FOREIGN KEY(REFERENCE_ID) REFERENCES BR_FLIGHT(REFERENCE_ID):
FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER(CUST_ID);


CREATE TABLE BR_FLIGHT (
REFERENCE_ID NVARCHAR(10) NOT NULL ,
FLIGHT_ID NVARCHAR (10) NOT NULL,
PRIMARY KEY(REFERENCE_ID, FLIGHT_ID),
FOREIGN KEY (REFERENCE_ID) REFERENCES BOOKING_REFERENCE(REFERENCE_ID)
FOREIGN KEY (FLIGHT_ID) REFERENCES FLIGHT(FLIGHT_ID)

);

Would the above sql work?? Thanks in advance and apologies for the shoddy diagram:)

上面的sql能用吗？？提前感谢并为劣质图表道歉:)

回答by Tony Andrews

Foreign keys have to match the primary/unique key they reference column for column. Since the primary key of BOOKING_REFERENCEis (REFERENCE_ID, CUST_ID), that means that the foreign key from BR_FLIGHTto BOOKING_REFERENCEmust consist of 2 columns also. That means you need to add CUST_IDto the BR_FLIGHTtable - either that or your BOOKING_REFERENCEprimary key is wrong and should just be (REFERENCE_ID).

外键必须匹配它们引用列的主键/唯一键。由于的主键BOOKING_REFERENCE是 ( REFERENCE_ID, CUST_ID)，这意味着来自BR_FLIGHTto的外键也BOOKING_REFERENCE必须由 2 列组成。这意味着您需要添加CUST_ID到BR_FLIGHT表中 - 或者您的BOOKING_REFERENCE主键是错误的，应该只是 ( REFERENCE_ID)。

That said, it doesn't make sense to have foreign keys defined in both directions as you do. The "child" table should reference the "parent" and not vice versa.

也就是说，像您一样在两个方向上定义外键是没有意义的。“子”表应该引用“父”，反之亦然。

回答by Milica Medic

When you reference composite primary key with a foreign key you must reference the whole key. In your case you should alter the BR_FLIGHT table and add the CUST_ID column

当您使用外键引用复合主键时，您必须引用整个键。在您的情况下，您应该更改 BR_FLIGHT 表并添加 CUST_ID 列

 ALTER TABLE BR_FLIGHT 

    ADD
       (
        CUST_ID NUMBER(10)NOT NULL
       );

And reference the full key as:

并将完整密钥引用为：

FOREIGN KEY  (REFERENCE_ID, CUST_ID) REFERENCES BOOKING_REFERENCE (REFERENCE_ID, CUST_ID)

Now DDL for BR_FLIGHT table will be:

现在 BR_FLIGHT 表的 DDL 将是：

CREATE TABLE BR_FLIGHT (
REFERENCE_ID NVARCHAR(10) NOT NULL ,
CUST_ID NUMBER(10)NOT NULL,
FLIGHT_ID NVARCHAR (10) NOT NULL,
PRIMARY KEY(REFERENCE_ID, FLIGHT_ID),
FOREIGN KEY  (REFERENCE_ID, CUST_ID) REFERENCES BOOKING_REFERENCE (REFERENCE_ID, CUST_ID)
);

As Tony Andrews pointed out you don't need the foreign part in the BOOKING_REFERENCE table. It should look like this:

正如 Tony Andrews 指出的，您不需要 BOOKING_REFERENCE 表中的外来部分。它应该是这样的：

CREATE TABLE BOOKING_REFERENCE (
REFERENCE_ID NVARCHAR(10) NOT NULL,
CUST_ID NUMBER(10)NOT NULL,
STATUS NVARCHAR (1), NOT NULL,
PRIMARY KEY(REFERENCE_ID, CUST_ID)
);

Hope this helps

希望这可以帮助

回答by Freddy Reyes

You have to put the UNIQUErestriction:

你必须把UNIQUE限制：

CREATE TABLE BOOKING_REFERENCE (
REFERENCE_ID NVARCHAR(10) NOT NULL UNIQUE,
CUST_ID NUMBER(10)NOT NULL,
STATUS NVARCHAR (1), NOT NULL,
PRIMARY KEY(REFERENCE_ID, CUST_ID),
FOREIGN KEY(REFERENCE_ID) REFERENCES BR_FLIGHT(REFERENCE_ID):
FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER(CUST_ID);


CREATE TABLE BR_FLIGHT (
REFERENCE_ID NVARCHAR(10) NOT NULL ,
FLIGHT_ID NVARCHAR (10) NOT NULL,
PRIMARY KEY(REFERENCE_ID, FLIGHT_ID),
FOREIGN KEY (REFERENCE_ID) REFERENCES BOOKING_REFERENCE(REFERENCE_ID)
FOREIGN KEY (FLIGHT_ID) REFERENCES FLIGHT(FLIGHT_ID)
);