3

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.

5
  • 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, 2013 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 ? Mar 3, 2013 at 3:27
  • 1
    this article might help you: simple-talk.com/sql/t-sql-programming/contiguous-time-periods
    – A-K
    Mar 3, 2013 at 4:32
  • So, you want to avoid having 2 rows with same PersonID and FinishDate being NULL? Mar 3, 2013 at 10:37
  • @ypercub that is exactly what i want. Mar 3, 2013 at 12:37

2 Answers 2

5

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

1
  • This does exactly what i want. Thank you very much. Mar 3, 2013 at 12:59
1

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

2
  • 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. Mar 3, 2013 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 ;-) Mar 3, 2013 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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