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.

Hygrometry

This design is a common pain. Have you considered changing the design so that the notionally 'deleted ' records are physically deleted from the table and perhaps moved to an 'archive' table? – Architectural

...because the inability to write a UNIQUE constraint to enforce a simple key should be considered a 'code smell', IMO. If you can't change the design (SQL DDL) because many other tables reference this table then I'll wager that your SQL DML also suffers as a result i.e. you have to remember to add ...AND Table.RecordStatus = 1' to most search conditions and join conditions involving this table and experiencing subtle bugs when it inevitably gets omitted on occasion. – Architectural

Answer 1

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;

Definite

i looked at table level check constraints but doesnt look there is any way to pass the values being inserted or updated to the function, do you know how to ? – Hygrometry

Okay, I posted a sample script that will help you prove what I'm talking about. I tested it and it works. If you look at the two commented lines, you'll see the message I get. Nota bene, in my implementation, I merely ensure that you cannot add a second item with the same Id which is active if there is already one active one. You could modify the logic such that if there is an active one, you cannot add any item with the same id. With this pattern, the possibilities are pretty much endless. – Definite

I'd prefer the same logic in a trigger. "a query in a scalar function... can create big problems if your CHECK constraint relies on a query and if more than one row is affected by any update. What happens is that the constraint gets checked once for each row before the statement completes. That means statement atomicity is broken and the function will be exposed to the database in an inconsistent state. The results are unpredicable and inaccurate." See: blogs.conchango.com/davidportas/archive/2007/02/19/… – Architectural

That's only partially true onedaywhen. The database behaves consistently and predictably. The check constraint will execute after the row is added to the table and before the transaction is committed by the dbms and you can count on that. That blog was talking about a pretty unique problem where you need to execute the constraint against a set of inserts rather than just one insert at a time. ashish is asking for a constraint on one insert at a time and this constraint will work accurately, predictably, and consistently. I'm sorry if this sounded terse; I was running out of characters. – Definite

This works great for inserts but doesn't seem to work for updates. E.G. Adding this after the other inserts works here when I didn't expect it to. INSERT INTO CheckConstraint VALUES (1, 'No ProblemsA', 2); update CheckConstraint set Recordstatus=1 where name = 'No ProblemsA' – Handyman

@D.Patrick, How to access CheckActiveCountConstraint in ALTER TABLE on Oracle? – Plagioclase

I'm not sure why but this didn't work for me in SQL Server 2016. I had to add OR RecordStatus = 2 for the constraint to also allow inserting records of status 2, so it had to be like ALTER TABLE CheckConstraint ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1) OR RecordStatus = 2); ... Perhaps it's because the original example is not concerned with adding records with status 2, but they will instead update them only from 1 to 2 at some point – Autogamy

Answer 2

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.

Following the creation of that index, a uniqueness violation will raise an arror:

Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.MyTable' with unique index 'MyIndex'. The duplicate key value is (9999).

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

Honewort

Note that SQL Server requires ansi_padding for filtered indexes, so make sure that this option is turned on by executing SET ANSI_PADDING ON before creating a filtered index. – Damalus

Answer 3

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.

Savadove

That's actually pretty clever Carl. It's not an answer to the question per se, but it's a good solution. If the table has a lot of rows, that could also speed up looking for an active record because you could look at the active record table. It would also speed up the constraint because the unique constraint uses an index as opposed to the check constraint I wrote below which has to execute a count. I like it. – Definite

Answer 4

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.

Sordino

This is a pretty good suggestion, and not that "hacky". Here is more information about this filtered index alternative. – Casabonne

It's a bad idea. The question is not it. – Pearly

I used a schemabound view once, and have never repeated the mistake. They can be a royal pain to work with. It's not that you have to recreate the view if you change the underlying table - you potentially have to do that for all views, at least in SQL server. It's that you cannot change the table without first dropping the view, which you might not be able to do without first dropping references to it. Oh, plus the storage could be problematic - either because of the space, or because of the cost it adds to insert and update. – Anjanette

Answer 5

For those still searching for a solution, I came accross a nice answer, to a similar question and I think this can be still useful for many. While moving deleted records to another table may be a better solution, for those who don't want to move the record can use the idea in the linked answer which is as follows.

• Set deleted=0 when the record is available/active.
• Set deleted=<row_id or some other unique value> when marking the row as deleted.

Obau

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.

Sweep

good idea, but there are no function based indexed in sql server thanks for the answer though – Hygrometry

Answer 7

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.

Balas