I have two date columns : "start date" and "finish date".
I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one primary key (composite in my case). Is there a way to do that?
To illustrate, there should be only one NULL for one part of the primary key. Here are the columns:
PeriodID | PersonID | StartDate | FinishDate 1 1 1/1/10 10/1/10 2 1 10/1/10 NULL 3 1 somedate MUST NOT BE NULL
And yes, "PersonID" is a foreign key.
I'm using SQL Server 2008.
I got a working answer,but other ways are wellcome too.