Compound foreign key with nullable column

Question

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.

Selfdefense

Triggers? It won't enforce, but can help you manage it. – Vampire

And to which value ID in MyTable should ParentID NULL reference? – Ehtelehud

When there is a value, ParentID should reference ParentID. When there isn't, well...that's the problem I'd like to solve. Since a compound foreign key doesn't do the trick, I'm looking for other options. – Selfdefense

Answer 1

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. */

Mcfadden

This just prevents nodes with null parents from being linked. It doesn't require that PreviousID refers to another node with a null parent. – Selfdefense

What I usually do for a complex constraint is to create a function that takes the relivant values for the new row and returns a boolean indicating whether the condition is met, and then call that function from a check constraint. That would do what you want for this system. I would write the function myself, but I must confess that I am a little bit uninspired by the form of the data. I don't really see what you are trying to accomplish. To make a real B-tree would require indexed direct-access memory or disk access. A DB already has sophisticated data structures. I don't get it. – Mcfadden

Use of UDFs in check constraints has integrity and performance problems, but it may be an option as a last resort. Data structures such as b-trees, linked lists, etc. are often modeled as DB tables. There are all kinds of reasons for storing data in a DB instead of directly to disk, the most prominent of which is referential integrity. – Selfdefense

Yes, DBs are great for storing data. But the DB uses a b-tree internally. Creating your own would introduce an unnecessary level of indirection in the data structure. A relational database should be designed to model real-world facts, not comp-sci data structures. – Mcfadden

And check constraints don't work when they're used incorrectly, just like anything else, but for a lot of situations they are an excellent tool when used correctly. – Mcfadden

An organizational chart resembles a b-tree, should it not be modeled in a DB? The resemblance of the data to a "comp-sci data structure" is coincidental. I only brought it up to help visualize the structure. Anyway, whether or not I should be storing this in a database is beside the point. The question is about how to enforce referential integrity when one of the participating columns can be null. – Selfdefense

Yes, it is beside the point. I merely pointed that out in a comment to say why I didn't fully implement the solution. My answer though, is that a check constraint may be used to enforce this requirement, and that is a correct answer. Good luck! – Mcfadden

It may work, but it has the problems I mentioned in a prior comment. I'll leave the question open in hopes that others may offer more ideal solutions. Thanks for your help. – Selfdefense