Conditional Single Restriction

Question

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

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

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

Besides writing triggers, can I do this?

I am using SQL Server 2005.

Answer 1

Add a control constraint similar to this. The difference is that you will 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 2

Here is the filtered index . From the documentation (highlighted mine):

A filtered index is an optimized, non-clustered index, especially suited to cover queries that are selected from a well-defined subset of data. It uses a filter predicate to index part of the rows in a table. A well-designed filtered index can improve query performance as well as lower maintenance and storage costs of the index compared to full tables.

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

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

In essence, this ensures that the ID unique when RecordStatus is 1 .

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

Answer 3

You can move deleted records to a table in which there is no restriction, and perhaps use a view with UNION from two tables to preserve the appearance of a separate table.

Answer 4

You can do it in a truly hacked way ...

Create a schema view in your table.

CREATE SELECT * FROM Table WHERE RecordStatus = 1 VIEW

Now create a unique constraint for the view with the fields you need.

One note about schema views, although if you change the underlying tables, you will have to recreate the view. There were a lot of mistakes because of this.

Answer 5

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

Answer 6

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

This will have the advantage that you do not need to explicitly check other rows in the table in your restriction, which can cause performance problems.

I have to say that I don’t know the SQL server at all, but I have successfully used this approach in Oracle.