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)

Record status 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 dont 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

many thanks

Answer 1

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 2

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 3

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 4

You could use a UNIQUE constraint if you use NULL as your deleted status.

Multiple rows with NULL are permitted by a UNIQUE constraint (at least in standard SQL -- I haven't confirmed this with a test against SQL Server 2005).

Answer 5

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 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.

Answer 7

Solution described here. Scroll down to "Use Computed Columns to Implement Complex Business Rules"

http://www.devx.com/dbzone/Article/30786/1954