Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

EDIT :

I got a working answer,but other ways are wellcome too.

share|improve this question
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

2 Answers 2

up vote 3 down vote accepted

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

-- only 1 FinishDate nullable 
CREATE UNIQUE INDEX IDXU_TAB1_1NULL ON TableName(FinishDate) WHERE FinishDate IS NULL;
-- or only 1 FinishDate is nullable for the same PersonId
CREATE UNIQUE INDEX IDXU_TAB2_1NULL ON TableName(PersonId, FinishDate) 
WHERE FinishDate IS NULL;

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

share|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).

share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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