1

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.

4

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).
  • Thank you for your assistance. I did as you say and it seems to work, but I'm confused as to why when I get SQL Sever to generate a Script Task to recreate the table afterwards that the Filtered Index does not appear in the code. I would have assumed it should add this code to so it recreates the Table exactly. – nytrex Sep 4 '15 at 8:14
  • @nytrex The filtered index definition will be left out if your script settings say to target SQL Server 2005 (this tricked me before too). There is also a bug that has been fixed and you may be affected. What is SELECT @@VERSION; output, and what is the build # in SSMS' Help > About? – Aaron Bertrand Sep 4 '15 at 11:58
  • @Aaron Bertrand many thanks. the first link you provided was very useful. Once I made changes to the 'Tools > Options > SQL Server Object Explorer > Scripting > Table and View options > Script Indexes to TRUE the Constraint now appears. – nytrex Sep 6 '15 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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