Multiple Column Constraint

Question

I have a table with three columns, the ID of the relation, the ID of the object related and the value of the object. The table may have as many repeated ID-Value relations except when the Value is 0 for the same ID.

              Correct                       Incorrect
    +--------+--------+-------+    +--------+--------+-------+
    | PK_ID  |   ID   | Value |    | PK_ID  |   ID   | Value |
    +--------+--------+-------+    +--------+--------+-------+
    |   1    |   1    |   1   |    |   1    |   1    |   1   |
    |   2    |   1    |   1   |    |   2    |   1    |   1   |
    |   3    |   2    |   0   |    |   3    |   2    |   0   |
    +--------+--------+-------+    |   4    |   2    |   0   |
                                   +--------+--------+-------+

The question is how can I enforce it not to allow a repeated ID-Value relation when the value is 0?

Can't do this with a check constraint. Use a filtered index or a view with a unique constraint dependant on version of SQL Server. — Martin Smith, Dec 26 '11 at 16:14
@Martin yes that's exactly what I need, but I'm on SQL Server 05 — PedroC88, Dec 26 '11 at 17:26
You need to use the indexed view technique then. There is an example in my answer in the "possible duplicate" link. — Martin Smith, Dec 26 '11 at 17:29

score 0 · Accepted Answer · edited May 23 '17 at 11:48

As pointed out by @Martin Smith in the comments of the question this is indeed similar to Simple CHECK Constraint not so simple and in the answer to that question (although it was for sql-08) there's an example solution for sql-00 and 05.

The idea is to create an index view to simulate a filtered unique index with the following code:

CREATE VIEW dbo.myTableView
WITH SCHEMABINDING
AS
SELECT ID
FROM dbo.myTable
WHERE Value = 0

GO 
CREATE UNIQUE CLUSTERED  INDEX ix ON dbo.myTableView(ID)

Multiple Column Constraint

1 Answers1