Adding a complex check constraint

Question

I am working on a table that has a compound primary key that includes a date.

Faculty

PersonId    StartDate    EndDate    Title ....

-------------------------------------------------

I will add a compound key consisting of the PersonID and StartDate. The business rule I am thinking about is allowing a person added as faculty, removed, and then added again later if needed. I am thinking a visiting professor. Or an adjunct who teaches an occasional course, but is not an active member of the faculty from one semester to the the next.

The end date will allow nulls, indicating a person is currently a faculty member.

I want to further constrain the data so a person can have only one row with an end date that is null.

Things I have considered:

UDF in a check constraint
Trigger (I hate triggers)
Application layer constraint (most likely what I will do at this point)

Note:

This is a hobby, work on this weekends kind of project
The intended data store is Azure SQL Database

This seems slightly off-topic to me. https://dba.stackexchange.com/ or SO, surely? — Lightness Races in Orbit, Nov 21 '15 at 15:02
It's kind of difficult to tell where your actual question is in this post. Were you asking how to make your primary key support nulls properly? i.e., does Lightness' answer solve your problem? — Ixrec, Nov 21 '15 at 15:26
@LightnessRacesinOrbit Unique constraint is not a where clause — paparazzo, Nov 21 '15 at 15:54
@Frisbee: I didn't say it was. http://sqlfiddle.com/#!9/b887b92/2/0 — Lightness Races in Orbit, Nov 21 '15 at 16:02
@LightnessRacesinOrbit I did try it out on sql-server and the question is tagged sql-server. That fiddle is MySQL. — paparazzo, Nov 21 '15 at 16:21
@Frisbee: Ah. What peculiar behaviour of MS SQL server. Apologies then. — Lightness Races in Orbit, Nov 21 '15 at 16:50
@LightnessRacesinOrbit, I dithered on whether to put this on dba.stackexchange.com. Maybe I am off topic, but ultimately posted hear since I was thinking this was more of a data design question. I post to the DBA site with more nuts and bolts kinds of questions. — Mike Henderson, Nov 21 '15 at 16:54
@lxrec Sorry if I am not clearer. Ultimately I was to allow many rows with a PersonId, StartDate, and EndDate. But only one row with where EndDate is null. — Mike Henderson, Nov 21 '15 at 17:01

paparazzo · Accepted Answer · 2015-11-21T17:06:54.077

3

At least for SQL Server you can use a unique constraint

Just put a unique constraint on PersonID, EndDate
You can use null in a unique constraint and it enforces allow one null but not more

I tested this out and it does exactly what you are asking for
The only possible problem is this would not allow two identical non-null EndDate also - but I suspect that is what you want

Unique Constraints and Check Constraints

Unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

Fiddle is down

CREATE TABLE [t1](
    [col1] [int] NOT NULL,
    [col2] [datetime] NOT NULL,
    [col3] [datetime] NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
    [col1] ASC,
    [col2] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_t1] ON [dbo].[t1]
(
    [col1] ASC,
    [col3] ASC
)
insert into t1 (1,1900-01-01,1900-01-01)
             , (1,1901-01-01,null)
             , (2,1900-01-01,1900-01-01)
             , (2,1902-01-01,1902-02-01)

edited Nov 21 '15 at 17:06

answered Nov 21 '15 at 15:54

paparazzo

1,937

I still wouldn't do it because it ruins portability. – Lightness Races in Orbit Nov 21 '15 at 16:51
@LightnessRacesinOrbit, This may not be good for portability, but it does appear to do enforce the constraint I am aiming for. – Mike Henderson Nov 21 '15 at 17:03
@MikeHenderson Then consider checking it as the answer – paparazzo Nov 21 '15 at 17:04
@Frisbee Consider it done. – Mike Henderson Nov 21 '15 at 17:08
As long as you know there is no chance that you'll ever want to switch RDBMSes. If you have a lot of data, you may not wish then to translate it all to fit a standard-compliant RDBMS. – Lightness Races in Orbit Nov 21 '15 at 17:48
@LightnessRacesinOrbit No chance ever? If it has to be translated on a slim chance it is not much work. – paparazzo Nov 21 '15 at 17:51
@Frisbee: It could be a huge amount of work, when the actual semantics of your 5TB database now need to be changed with a bunch of UPDATE statements and re-indexing, that now take hours or even days to run, just because you insisted upon using NULL instead of 0000-00-00 00:00:00 for frankly no good reason. And that's not even considering the cost of altering the application code that sits on top of it. What this suggestion does it to deliberately use a potentially costly non-portable solution for zero gain. – Lightness Races in Orbit Nov 21 '15 at 18:14
@LightnessRacesinOrbit Easy dude. I really doubt a hobby project on a teacher schedule is 5MB let alone 5TB. It is a single update statement and a single alter table statemetn. I am not insisting on anything. I am just answering the stated question. – paparazzo Nov 21 '15 at 18:30
I'm not your "dude". I'm just giving my opinion, and explaining it as challenged. You'll notice I did not cast any downvote. – Lightness Races in Orbit Nov 21 '15 at 18:34
@LightnessRacesinOrbit Challenged? Notice I did not cast a down vote to your incorrect answer. If you want to down vote me for a correct answer because I am supporting a potentially costly non-portable solution for zero gain then fine. And I don't retaliate. – paparazzo Nov 21 '15 at 18:58
@Frisbee: No, I did not downvote because I don't want to. Your answer is correct. And I don't "retaliate" (wtf?!). I'm just discussing the portability situation. That's it, that's all. No need for the theatrics. – Lightness Races in Orbit Nov 21 '15 at 19:17

Adding a complex check constraint

1 Answers1