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 ?
You should do this with a foreign key constraint.
You can create a check constraint with the following definition
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)
This is trivial to do with a Foreign Key constraint
Though I'd probably introduce a surrogate key to the
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.
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