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.

  • 1
    Your question makes no sense. A table will only have one row for each primary key value (composite in your case), given that the definition doesn't allow duplicates. How can there be multiple values (one of which being NULL)? Are you taking about a 2nd table that references the first table? Edit your question with table definitions and an example. – Philᵀᴹ Mar 3 '13 at 0:45
  • Do I understand that for finish date you want to allow the same value to be assigned to multiple people except for NULL which you only want to allow once? Or are you wanting a unique combination of values. Say unique for a combination of periodid, personid, startdate, finishdate ? – Kenneth Fisher Mar 3 '13 at 3:27
  • 1
    this article might help you: simple-talk.com/sql/t-sql-programming/contiguous-time-periods – A-K Mar 3 '13 at 4:32
  • So, you want to avoid having 2 rows with same PersonID and FinishDate being NULL? – ypercubeᵀᴹ Mar 3 '13 at 10:37
  • @ypercub that is exactly what i want. – user1847916 Mar 3 '13 at 12:37

If I understand your question properly, SQLServer's partial index can do the job :

-- only 1 FinishDate nullable 
-- or only 1 FinishDate is nullable for the same PersonId

Just to repeat what's already said :
1. any column which is part of PK cannot be nullable
2. as Chris Travers mentioned in his comment to another answer, SQLServer treats NULLs in a different ways than other RMDS when it comes to unique constraints

| improve this answer | |
  • This does exactly what i want. Thank you very much. – user1847916 Mar 3 '13 at 12:59

You can't have nulls in a primary key column. You can, however, create a unique index/constraint on a column, and that column will then allow only a single null for the whole table (in addition to requiring unique values).

| improve this answer | |
  • Just to be clear,i want "FinishDate" to be NULL,which is not a part of the primary key. And i dont want it to be unique becose some other person can have the same finish date. – user1847916 Mar 3 '13 at 1:54
  • 4
    I just want to point out that a "single null for the whole table" is specific to SQL Server (which is fair since this an SQL Server question). PostgreSQL, for example, treats nulls as distinct for UNIQUE purposes and therefore you have to add an additional partial unique index. I just wanted to note this so that nobody gets in trouble trying this on other db's ;-) – Chris Travers Mar 3 '13 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.