非主键的外键 [英] Foreign Key to non-primary key

问题描述

我有一个包含数据的表，其中一行需要存在于另一个表中.所以，我想要一个外键来保持参照完整性.

I have a table which holds data, and one of those rows needs to exist in another table. So, I want a foreign key to maintain referential integrity.

CREATE TABLE table1
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT NOT NULL,
   SomeData VARCHAR(100) NOT NULL
)

CREATE TABLE table2
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT NOT NULL,
   MoreData VARCHAR(30) NOT NULL,

   CONSTRAINT fk_table2_table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID)
)

但是，正如您所看到的，我作为外键指向的表，该列不是 PK.有没有办法创建这个外键，或者有更好的方法来保持这种参照完整性?

However, as you can see, the table I foreign key to, the column isn't the PK. Is there a way to create this foreign key, or maybe a better way to maintain this referential integrity?

推荐答案

如果你真的想为非主键创建外键，它必须是一个具有唯一约束的列.

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.

来自在线图书:

FOREIGN KEY 约束不必只链接到 PRIMARY另一个表中的 KEY 约束；它也可以定义为参考另一个表中 UNIQUE 约束的列.

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

因此，在您的情况下，如果您使 AnotherID 唯一，它将被允许.如果您不能应用唯一约束，那您就不走运了，但仔细想想这确实有意义.

So in your case if you make AnotherID unique, it will be allowed. If you can't apply a unique constraint you're out of luck, but this really does make sense if you think about it.

尽管如前所述，如果您有一个非常好的主键作为候选键，为什么不使用它呢?

Although, as has been mentioned, if you have a perfectly good primary key as a candidate key, why not use that?

这篇关于非主键的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！