conditional unique constraint

Question

I have a situation where i need to enforce a unique constraint on a set of columns, but only for one value of a column.

So for example I have a table like Table(ID, Name, RecordStatus).

RecordStatus can only have a value 1 or 2 (active or deleted), and I want to create a unique constraint on (ID, RecordStatus) only when RecordStatus = 1, since I don't care if there are multiple deleted records with the same ID.

Apart from writing triggers, can I do that?

I am using SQL Server 2005.

Answer 1

You could move the deleted records to a table that lacks the constraint, and perhaps use a view with UNION of the two tables to preserve the appearance of a single table.

Answer 2

You can do this in a really hacky way...

Create an schemabound view on your table.

CREATE VIEW Whatever SELECT * FROM Table WHERE RecordStatus = 1

Now create a unique constraint on the view with the fields you want.

One note about schemabound views though, if you change the underlying tables you will have to recreate the view. Plenty of gotchas because of that.

Answer 3

Behold, the filtered index. From the documentation (emphasis mine):

A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

And here's an example combining a unique index with a filter predicate:

create unique index [MyIndex]
on [MyTable]([ID])
where [RecordStatus] = 1

This essentially enforces uniqueness of ID when RecordStatus is 1.

_{Note: the filtered index was introduced in SQL Server 2008. For earlier versions of SQL Server, please see this answer.}

Answer 4

Add a check constraint like this. The difference is, you'll return false if Status = 1 and Count > 0.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

CREATE TABLE CheckConstraint
(
  Id TINYINT,
  Name VARCHAR(50),
  RecordStatus TINYINT
)
GO

CREATE FUNCTION CheckActiveCount(
  @Id INT
) RETURNS INT AS BEGIN

  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
  RETURN @ret;

END;
GO

ALTER TABLE CheckConstraint
  ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1));

INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);

INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);
-- Msg 547, Level 16, State 0, Line 14
-- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);

SELECT * FROM CheckConstraint;
-- Id   Name         RecordStatus
-- ---- ------------ ------------
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  1
-- 2    Oh no!       1
-- 2    Oh no!       2

ALTER TABLE CheckConstraint
  DROP CONSTRAINT CheckActiveCountConstraint;

DROP FUNCTION CheckActiveCount;
DROP TABLE CheckConstraint;

Answer 5

Because, you are going to allow duplicates, a unique constraint will not work. You can create a check constraint for RecordStatus column and a stored procedure for INSERT that checks the existing active records before inserting duplicate IDs.

Answer 6

If you can't use NULL as a RecordStatus as Bill's suggested, you could combine his idea with a function-based index. Create a function that returns NULL if the RecordStatus is not one of the values you want to consider in your constraint (and the RecordStatus otherwise) and create an index over that.

That'll have the advantage that you don't have to explicitly examine other rows in the table in your constraint, which could cause you performance issues.

I should say I don't know SQL server at all, but I have successfully used this approach in Oracle.