Using constraints to restrict values in a column

From SQL Server Wiki
Jump to navigation Jump to search

You can use a check constraint to restrict the values permitted in a column, but it is sometimes better to define the values in a separate ‘lookup’ table and enforce the data restrictions with a foreign key constraint. Care should to be taken to use the appropriate method.

When the required restriction is to a set of values which changes from time to time and can comfortably be enumerated using a 'lookup' table to enumerate the set and enforcing the restriction with a foreign key constraint makes the restriction much easier to maintain and will also avoid a code change every time a new value is added to or removed from the permitted range, as would be the case with a check constraint.

When the required restriction is a simple range or size restriction it is much more effective to use a check constraint. For example a restriction on a column of type int that the value be positive and nonzero is more efficiently implemented by a check constraint CHECK(col > 0) than by creating a lookup table to contain all int values greater than zero and enforcing the restriction with a foreign key constraint; similarly a restriction on a varchar column that the value is a string of between 36 and 128 ascii characters can be practically enforced by the type (varchar(128)) of the column to restrict the length to that maximum and a check constraint CHECK(len(col)>=36) to enforce the restriction of the minimum length restriction but probably not by enforcing the minimum length by a foreign key pointing to a table containing every possible character string whose length is between 36 and 128 characters, which would require vast storage, more than a quadrillion yotta-yotta-(keep going to 12 yottas)-bytes just for that one table.