HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Only one NULL for one part of primary key

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
primarynullpartoneforonlykey

Problem

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.

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#35824, answer score: 5

Revisions (0)

No revisions yet.