[SQL 2000]Composite key (combination of PK from 2 tables) but allow null in the 2nd c

[dee-u]

To illustrate it better here is a sample data:

Column1 Column2
R1 NULL
R2 NULL
R2 X1
R2 X2
R3 NULL


As you can see the combined columns is unique, that is the requirement, is there a workaround that I can make a Composite Key out of those two tables?

Another thing, those two columns are PK from other tables, with the first column there is no problem cascading updates, but not so with the 2nd column, aside from a trigger is there any other trick that will let me cascade update the other tables using the reference from the 2nd column?

TIA

[jmcilhinney]

I'm fairly sure what you're trying to do is not possible. I think you'd have to add an extra identity column to act as PK for the table and then make your two FK columns a unique key for the table, which I believe will allow nulls.

[GaryMazzone]

PKs can not contain NULL values, so Jmcilhinney is correct what you want to do is not possible. Not leaving a null in the second column of the key.

[jmcilhinney]

Here's an example of what I was talking about. Note that I set "Enforce Foreign Key Constraint" to False for the relationship between Table2 and Table3 in order to allow NULLs in the Table2ID column.