Sql-server – Enforce a single NULL end date per foreign key value using an index

constraintforeign keysql server

I want to implement a Check Constraint across a date range and foreign key.

I have a table created as:

  • Primary Key
  • Foreign Key
  • DateStart
  • DateEnd

The ForeignKey can appear many times in the table as long as there is a value in the EndDate. But there should only ever be one entry for the ForeignKey where the DateEnd is Null (the active version).

Apologies if this is simple, but I've been trying to use SQL Server GUI to put in an Constraint expression, but I can't get it to work.

Any help, or pointers to help would be appreciated.

Best Answer

You can do this with a unique filtered index.

Table setup.

create table dbo.OtherTable
(
  FK int primary key
);

create table dbo.YourTable
(
  PK int identity primary key,
  FK int references dbo.OtherTable(FK),
  DateEnd date
);

Add the unique index on FK filtered on DateEnd is null.

create unique index UX_YourTable_FK_DateEnd on dbo.YourTable(FK) 
  include (DateEnd)
  where DateEnd is null;

Add one row to OtherTable.

insert into dbo.OtherTable(FK) values(1);

Add a couple of rows to YourTable.

insert into dbo.YourTable(FK, DateEnd) values(1, '2001-01-01');
insert into dbo.YourTable(FK, DateEnd) values(1, null);

Add another row with a value in DateEnd will work just fine.

insert into dbo.YourTable(FK, DateEnd) values(1, '2001-01-01');

Add a second row with null in DateEnd will fail.

insert into dbo.YourTable(FK, DateEnd) values(1, null);

Msg 2601, Level 14, State 1, Line 33 Cannot insert duplicate key row in object 'dbo.YourTable' with unique index 'UX_YourTable_FK_DateEnd'. The duplicate key value is (1).

Create Filtered Indexes
Create Unique Indexes

When scripting indexes from SSMS:

  1. Make sure "Script Indexes" is True in the Advanced Scripting Options. It is default True if you script the entire database but False if you specify a specific table.
  2. Also make sure the setting under "Tools / Options / SQL Server Object Explorer / Scripting" is set to "Script for server version 2008" (at least) otherwise the filtering condition will be silently omitted (Connect bug report).
Related Question