sql - 无法创建 FK 约束

Question

我有2张 table :

TableA
------
TableAID INT IDENTITY Primary Key
TableBID INT NULL

I add a unique constraint on TableBID
ALTER TABLE TableAID ADD CONSTRAINT xx UNIQUE ([TableBID])

TableB
------
TableBID INT IDENTITY Primary Key

现在，当我删除 TableA 行时，我也需要删除 TableB，所以我尝试添加带有级联删除的 FK

ALTER TABLE TableB ADD CONSTRAINT yy FOREIGN KEY (TableBID) 
REFERENCES TableA (TABLEBID) ON DELETE CASCADE

但我收到此错误:

Msg 1788, Level 16, State 0, Line 1
Cascading foreign key 'yy' cannot be created where the referencing column 'TableB.TableBID' is an identity column.

Answer 1

TableB.TableBID 不能同时是一个 IDENTITY 列(它是独立计算的)，同时又是另一个表中某个列的外键。

我认为你已经扭转了你们的关系并且实际上想要:

ALTER TABLE TableA ADD CONSTRAINT yy FOREIGN KEY (TableBID) 
REFERENCES TableB (TABLEBID) ON DELETE CASCADE