开发者

Compound foreign key with nullable column

开发者 https://www.devze.com 2023-01-20 11:11 出处:网络
In the following table, is there a way to ensure PreviousID always references an ID in a row with a matching ParentID, or, if ParentID is null, it is also null in the referenced row?

In the following table, is there a way to ensure PreviousID always references an ID in a row with a matching ParentID, or, if ParentID is null, it is also null in the referenced row?

CREATE TABLE MyTable (
  ID int not null identity(1,1) primary key,
  ParentID int null foreign key references MyTable (ID),
  PreviousID int null foreign key reference MyTable (ID),
    foreign key (ParentID, PreviousID) references MyTable (ParentID, ID)
)

An example:

+-ID-+-ParentID-+-PreviousID-+  
|  1 |   NULL   |    NULL    |  
|  2 |     1    |    NULL    |  
|  3 |   NULL   |      2     | <-- shouldn't be possible, should have to refer to ID where ParentID is null
+----+----------+------------+  

Is there a way to enforce this?

UPDATE: For those wondering, the compound foreign key doesn't enforce this for the following reason (copied from 开发者_开发知识库MSDN):

A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.

UPDATE: In case it helps to visualize the data structure being represented, it's a B-tree where nodes having the same parent compose a linked list. I'm trying to enforce that each 'previous' pointer in the linked list points to another node with the same parent. In the case that the parent is null, it should point to another node where the parent is also null.


Try this:

CREATE TABLE MyTable ( 
  ID int not null identity(1,1) primary key, 
  ParentID int null foreign key references MyTable (ID), 
  PreviousID int null foreign key references MyTable (ID), 
    foreign key (ParentID, PreviousID) references MyTable (ParentID, ID),
    unique (ParentID, ID),  /* Required for foreign key */
  check (PreviousID is null or ParentID is not null)  /* enforeces requested constraint */
) 

Results:

insert into MyTable (ParentID, PreviousID) values (null, null) /* (1 row(s) affected) */
insert into MyTable (ParentID, PreviousID) values (1, null) /* (1 row(s) affected) */
insert into MyTable (ParentID, PreviousID) values (null, 2) /* The INSERT statement conflicted with the CHECK constraint. 
    The statement has been terminated. */
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号