Combining UNIQUE and CHECK Constraints

Question

I have a table with columns ab and c, and if c is false, I only want to allow insertions if columns a and b are unique, but if c is true, then a and b should not be unique.

Example: In the table there can be only one (foo, bar, false), but not limited by the number (foo, bar, true).

I tried something like CONSTRAINT blah UNIQUE (a, b) AND CHECK (C TRUE), but I can not understand the correct syntax.

Answer 1

• Create an indexed view that returns a and b with the WHERE C = false filter WHERE C = false , and then create a unique index in the view. This is a general approach.

• If you have SQL Server 2008, create a unique filtered index instead

• Stored procedure

• Trigger (before or after)

Answer 2

There are unique restrictions on all lines, and there is no way to specify only certain lines.

Check restrictions only for checking within one line.

The only ways to achieve your requirements is to do any of the following:

1) create a stored procedure from which all INSERTS are made. Here you can check all your logic. However, programs or rogue users can avoid using this and defeat your logic.

2) create a trigger that checks your logic and causes invalid insertions / updates to fail. Must be written to process a rowset at a time

3) create a control constraint that calls a user-defined function that will check your checks. This is considered bad practice and should be avoided because they are very slow and may not work for multi-line updates.

I recommend option # 2, trigger, because that’s exactly what triggers were created for.

Answer 3

You can always create a trigger that checks the necessary conditions before inserting.

Answer 4

You can use a table constraint (depending on the SQL engine — it is unclear which one you are using) to make sure there are not too many tuples of a particular type.

In Firebird 2.1:

 fb> CREATE TABLE so2587151 ( > a VARCHAR(16) NOT NULL, > b VARCHAR(16) NOT NULL, > c VARCHAR(1) NOT NULL CHECK (c in ('T', 'F')), > CONSTRAINT so2587151_only_one_false CHECK ( -- begin CONSTRAINT > NOT EXISTS ( SELECT a, b, COUNT('x') > FROM so2587151 > WHERE c = 'F' > GROUP BY 1, 2 > HAVING COUNT('x') >= 1 ) > ) -- end CONSTRAINT > ); fb> INSERT INTO so2587151(a,b,c) VALUES ('foo', 'bar', 'T'); fb> INSERT INTO so2587151(a,b,c) VALUES ('foo', 'bar', 'T'); fb> INSERT INTO so2587151(a,b,c) VALUES ('foo', 'bar', 'F'); fb> INSERT INTO so2587151(a,b,c) VALUES ('foo', 'bar', 'F'); Error: Operation violates CHECK constraint on view or table -Operation violates CHECK constraint SO2587151_ONLY_ONE_FALSE on view or table SO2587151 -At trigger 'CHECK_15' fb> SELECT * FROM so2587151; A | B | C ============== foo | bar | T foo | bar | T foo | bar | F 

As you can see from the restriction violation error message, this is implemented from the point of view of the Firebird launch mechanisms under the hood.