I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance.
Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better.
First, write a trigger.
Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record.
CREATE TABLE test (
ssn CHAR(9) NULL,
testId INT identity(1,1) NOT NULL,
CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9))
END, UNIQUE (ComputedConstraint))
Your right, I should change the computed column to varchar(36) and CAST(newid() as varchar(36)) instead of using the pk identity value if the SSN is null or multiply the identity * -1 to make it negative. I've seen problems with duplicates using datetimes in the past.
I'm confused by your reference question. The computed column should never be referenced. It's only there to check uniqueness without using a trigger. Does this answer you question?
Last edited by peterlemonjello; 07-16-04 at 13:12.