conditional unique constraint

Problem

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 instance, I have a table named Table (ID, Name, RecordStatus).

I want to create a unique constraint on (ID, RecordStatus) just when RecordStatus = 1, because I don’t care if there are numerous deleted records with the same ID.

Apart from writing triggers, can I do that?

I am using SQL Server 2005.

Asked by np-hard

Solution #1

Take a look at the filtered index. The following is taken from the documentation (emphasis mine):

Here’s an example of how to use a unique index in conjunction with a filter predicate:

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

When RecordStatus is 1, this effectively enforces ID uniqueness.

An arror: uniqueness violation will be raised following the construction of that index.

The filtered index first appeared in SQL Server 2008. Please see this answer for older versions of SQL Server.

Answered by canon

Solution #2

Create a check constraint similar to this. The difference is that if Status = 1 and Coun = 0, you’ll get false.

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;

Answered by D. Patrick

Solution #3

To keep the appearance of a single table, you may relocate the deleted records to a table that doesn’t have the constraint, and then use a view with UNION of the two tables.

Answered by Carl Manaster

Solution #4

You can accomplish it in a really clumsy manner…

On your table, create a schemabound view.

CREATE A VISION SELECT * FROM TABLE WHEREEVER 1 if RecordStatus = 1 if RecordStatus = 1 if

Now, on the view, establish a unique constraint with the fields you require.

One thing to keep in mind with schemabound views: if the underlying tables change, you’ll have to rewrite the view. As a result, there are several gotchas.

Answered by Min

Solution #5

Because you’ll be allowing duplicates, a unique constraint will be useless. Before entering duplicate IDs, you can create a check constraint for the RecordStatus column and a stored procedure for INSERT that checks for existing active records.

Answered by ichiban

Post is based on https://stackoverflow.com/questions/866061/conditional-unique-constraint