Sql-server – check constraint that has dynamic list

sql server

I need to create a check constraint, the problem i face is that the list of values accepted by the constraint changes in the future (ex:now "red,green and blue" in the next month "red,green,blue and white"). How to do this ?

Best Answer

You should do this with a foreign key constraint.

You can create a check constraint with the following definition 

CREATE TABLE T
(
Color varchar(10) CHECK (Color in ('red','green','blue'))
)

But there is no way of altering a check constraint definition without dropping it and recreating it (thus requiring all rows to be revalidated against the new definition)

To modify a CHECK constraint, you must first delete the existing CHECK constraint and then re-create it with the new definition.

This is trivial to do with a Foreign Key constraint 

CREATE TABLE Colors
  (
     Color VARCHAR(10) PRIMARY KEY
  )

INSERT INTO Colors
VALUES      ('red'),
            ('green'),
            ('blue')

CREATE TABLE T
  (
     Color VARCHAR(10) REFERENCES Colors
  )

Though I'd probably introduce a surrogate key to the Colors table rather than storing the string repeatedly in the main table.

I have come across the argument before that using a check constraint is somehow "more correct" than using foreign keys and a lookup table but the advantages of the lookup table to me are.

  1. Easier and more efficient to add items to the list.
  2. Easier to get a distinct list of allowable colours (e.g. to display in a listbox in your application)
  3. Using a fixed length integer surrogate key can have performance advantages compared to a variable length string both in terms of reducing row size and avoiding fragmentation on updates.

NB: It is possible to have a check constraint reference a scalar UDF that in turn references a table but this approach should be avoided. It does not simulate a foreign key correctly (e.g. does not validate on DELETE FROM Colors)

Related Question