Combining the UNIQUE and CHECK constraints

Question

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

Example: There can only be one (foo, bar, false) in the table, but no limit on how many (foo, bar, true) there can be.

I tried something like CONSTRAINT blah UNIQUE (a,b) AND CHECK (C is TRUE) but I can't figure out the correct syntax.

Answer 1

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

Answer 2

Unique constraints are for all rows, and there is no way to specify only some rows.

Check constraints are only for validation within a single row.

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

1) create a stored procedure where all INSERTs are made from. You can validate all of your logic here. However rogue programs/users can avoid using this and defeat your logic.

2) create a trigger that validates your logic and causes invalid inserts/updates to fail. Should be written to handle a set of rows at a time

3) create a check constraint that calls a user defined function that does your validation checks. This is considered a bad practice and should be avoided because they are very slow and may fail for multi-row updates.

I recommend option #2, trigger, because this is exactly what triggers were designed for.

Answer 3

1. Create an indexed view returning a and b with a filter WHERE C = false, then create a unique index on the view. This is a general approach

2. If you have SQL Server 2008, then create a unique filtered index instead

3. Stored procedure

4. Trigger (before or after)

Answer 4

You can use a table constraint (depending on SQL engine -- not clear which one you're using) to make sure there aren't too many tuples of a particular kind.

Under 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 constraint violation error message, this is implemented in terms of Firebird's trigger mechanisms under the hood.