本文介绍了非主键的外键的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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 约束的列.

因此,在您的情况下,如果您使 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?

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

08-30 03:19