SQL Server：使用多列外键创建表

SQL Server : creating a table with a multi-column foreign key

提问by Simon

I have to create a set of tables in SQL Server.

我必须在 SQL Server 中创建一组表。

Those are:

那些是：

• chips, for identifying a chip to be implanted to an animal
• signals, because every chip generates a signal once every hour
• signal receivers, stations which can receive a signal if a chip is in its radius
• signal receivings, for signals that have been received by a receiver

• chips, 用于识别要植入动物的芯片
• signals，因为每个芯片每小时产生一次信号
• signal receivers, 如果芯片在其半径内，则可以接收信号的站点
• signal receivings, 对于接收器接收到的信号

I have a problem in creating those tables.

我在创建这些表时遇到问题。

This is my code:

这是我的代码：

CREATE TABLE CHIPS (
    ID INT PRIMARY KEY,
    ...(not related attributes)...
    );

CREATE TABLE RECEIVERS (
    ID_receiver INT PRIMARY KEY,
    ...(some other attributes, not related to the problem)...
    );

CREATE TABLE SIGNALS (
    ID_chip INT FOREIGN KEY REFERENCES CHIPS, 
    Signal_no INT, 
    Date DATETIME, 
    PRIMARY KEY (ID_chip, Signal_no)
    );

CREATE TABLE SIGNAL_RECEIVINGS (
    ID_receiver INT REFERENCES RECEIVERS ,
    Id_chip INT REFERENCES SIGNALS(ID_chip), 
    Signal_no INT REFERENCES SIGNALS(Signal_no), 
    PRIMARY KEY (Id_chip, Signal_no, ID_receiver ) 
    );

My problem is that I don't know how to make a proper key for the SIGNAL_RECEIVERS. I want it to be (Id_chip, Signal_no, ID_receiver)because only then it would be unique, but SQL prints out an error :

我的问题是我不知道如何为SIGNAL_RECEIVERS. 我希望它是(Id_chip, Signal_no, ID_receiver)因为只有这样它才会是唯一的，但 SQL 打印出一个错误：

The number of columns in the referencing column list for foreign key 'FK__SIGNAL_R__Si_no__5C187C73' does not match those of the primary key in the referenced table 'SIGNALS'.

外键“FK__SIGNAL_R__Si_no__5C187C73”的引用列列表中的列数与引用表“SIGNALS”中主键的列数不匹配。

Could someone help me out on this one?

有人可以帮我解决这个问题吗？

回答by Obsidian Phoenix

Your problem isn't in the Primary Key on SIGNAL_RECEIVINGS, that will work just fine. Your problem is that you have id_chip with a foreign key to SIGNALS, and Signal_no with a foreign key to SIGNALS, both referencing a single column. Given that the PK on SIGNALSis a composite key, your FK on SIGNAL_RECEIVINGSneeds to also be a composite to the table.

您的问题不在 上的主键上SIGNAL_RECEIVINGS，那会正常工作。您的问题是您的 id_chip 外键为 to SIGNALS，Signal_no 外键为 to SIGNALS，两者都引用单个列。鉴于 PK onSIGNALS是一个组合键，您的 FK onSIGNAL_RECEIVINGS也需要是表的组合。

You need to create a single FK, pointing at both columns:

您需要创建一个 FK，指向两列：

CREATE TABLE SIGNAL_RECEIVINGS (
  ID_receiver INT REFERENCES RECEIVERS ,
  Id_chip INT, 
  Signal_no INT, 
  Foreign Key (id_chip, signal_no) references Signals (id_chip, signal_no),
  PRIMARY KEY (Id_chip, Signal_no, ID_receiver ) 
  );

That said, I think your use of Composite Primary Keys is going to get out of control they way you are using them all over the place. Pretty soon (carrying on the code that you have in place), you are going to end up with composite keys potentially tens of columns long. I'd direct you to thisanswer from another question detailing why simply adding an Identity column to your code will help simplify your keys down a fair bit.

就是说，我认为您对复合主键的使用将失去对您在所有地方使用它们的方式的控制。很快（继续执行您现有的代码），您最终会得到可能有数十列长的复合键。我会引导您从另一个问题中找到这个答案，详细说明为什么简单地向您的代码添加一个 Identity 列将有助于简化您的密钥。

Composite keys can be handy in places where the data has to appear multiple places anyway, but if you are adding those columns simply to satisfy the key constraints, then a single Identity column would suit much better.

复合键在数据必须出现在多个位置的地方很方便，但如果您添加这些列只是为了满足键约束，那么单个 Identity 列会更适合。

回答by Bogdan Sahlean

1) Composite FKs including nullable columns are very trickybecause they allows orphans rows.

1) 包含可空列的复合 FK非常棘手，因为它们允许孤立行。

Example:

例子：

CREATE TABLE dbo.X (
    ColA INT not null,
    ColB INT not null,
    PRIMARY KEY (ColA, ColB)
);
CREATE TABLE dbo.Y (
    ColA int null,
    ColB int null,
    FOREIGN KEY(cola, colb)
    REFERENCES dbo.X(ColA, ColB)
);
INSERT dbo.Y VALUES (NULL,123);
GO
/*
(1 row(s) affected)
*/

SELECT * FROM dbo.Y 
ColA        ColB
----------- -----------
NULL        123

As you can see, even we don't have any rows in dbo.X(PK) we have one [orphan] row into dbo.Y(FK). And this is possible because we've inserted a NULLinto ColA. So, if we want that SQL Server to check referential integrity in such cases (composite FK with nullable columns) then all values of a composite foreign key must be NOT NULL(ColA and ColB in this example).

如您所见，即使我们在dbo.X(PK) 中没有任何行，我们也有一个 [孤儿] 行到dbo.Y(FK) 中。这是可能的，因为我们已将 a 插入NULL到ColA. 因此，如果我们希望 SQL Server 在这种情况下检查参照完整性（具有可空列的复合 FK），那么复合外键的所有值都必须是NOT NULL（本例中的 ColA 和 ColB）。

2) So there are 3 options: (i) your business allows such orphan rows, (ii) you create composite FK on mandatory (NOT NULL) columns

2) 因此有 3 个选项：(i) 您的业务允许此类孤立行，(ii) 您在强制 ( NOT NULL) 列上创建复合 FK

CREATE TABLE dbo.SIGNAL_RECEIVINGS (
  ID_receiver INT REFERENCES dbo.RECEIVERS(ID_receiver) ,
  Id_chip INT NOT NULL,     -- Mandatory column
  Signal_no INT NOT NULL,   -- Mandatory column
  FOREIGN KEY (id_chip, signal_no) REFERENCES dbo.Signals (id_chip, signal_no),
  ...
);

or (iii) you create a simple FK.

或 (iii) 您创建了一个简单的 FK。

回答by Amir Keshavarz

Try this:

尝试这个：

CREATE TABLE SIGNAL_RECEIVINGS (
ID_receiver INT REFERENCES RECEIVERS ,
Id_chip INT  , 
Signal_no INT , 
PRIMARY KEY (Id_chip, Signal_no, ID_receiver ) ,
foreign key (id_chip,signal_no) references signals(id_chip,signal_no)
);