我们可以有一个外键而不是其他表中的主键吗?

it is written in every book that foreign keys are actually primary key in some other table but can we have a foreign key which is not primary key in any other table

在每一本书中都提到外键实际上是其他表中的主键但是我们能不能有一个外键不是其他表中的主键

4 个解决方案

#1

Yes - you can have a foreign key that references a unique index in another table.

是的——您可以有一个外键来引用另一个表中的唯一索引。

CREATE UNIQUE INDEX UX01_YourTable ON dbo.YourTable(SomeUniqueColumn)

ALTER TABLE dbo.YourChildTable
   ADD CONSTRAINT FK_ChildTable_Table
   FOREIGN KEY(YourFKColumn) REFERENCES dbo.YourTable(SomeUniqueColumn)

#2

By definition a foreign key must reference a candidate key of some table. It doesn't necessarily have to be the primary key.

根据定义，外键必须引用某个表的候选键。它不一定非得是主键。

As a matter of detail the constraint called a FOREIGN KEY in SQL isn't exactly equivalent to the textbook definition of a foreign key in the relational model. SQL's FOREIGN KEY constraint differs because:

具体来说，SQL中称为外键的约束并不完全等同于关系模型中外键的教科书定义。SQL的外键约束不同，因为:

it can reference any set of columns subject to a uniqueness constraint even if they are not candidate keys (superkeys or nullable columns for example).
它可以引用受唯一性约束的任何列集，即使它们不是候选键(例如超键或可空列)。
it may include nulls, in which case the constraint is not enforced
它可能包含nulls，在这种情况下约束不会被强制执行
its syntax depends on column order, so a fk constraint on (A,B) referencing (A,B) is different to a constraint on (B,A) referencing (A,B).
它的语法取决于列的顺序，所以(a,B)引用的fk约束(a,B)与(B, a)引用的约束(a,B)不同。

#3

Yes , There can be a foreign key which is unique key in other table as Unique key is subset of primary key but not the exact primary key.

是的，可以有一个外键，它是其他表中的唯一键，因为唯一键是主键的子集，而不是确切的主键。

So that's possible that foreign key is unique key in aother table.

所以外键可能是另一个表中的唯一键。

#4

General standard answer is no. It is only possible if foreign key refers any column uniquely in other table. That means foreign key must be the candidate key in other table and primary key is also a candidate key the table.

一般的标准答案是否定的。只有当外键引用其他表中唯一的列时，才可能使用外键。这意味着外键必须是其他表中的候选键，主键也是表中的候选键。

#1