Hello,

What are some of the pros & cons of using a field (CHECK) constraint vs. a very small table (holding say, 3-5 values)? I've seen a few examples in books & tutorials where they went either way, but not much explanation as to the benefits one way or the other.

A specific example of what I'm looking at is say I want to have a table that tracks various information about club members. One of the fields would be where they bought their membership at. There are not many options here; one of three stores in town or directly through the club. All it normally gets used for is when we print out reports of membership sales in the last given time period (month, year, etc.) we like to know which place is selling the most and make sure they have sufficient applications, etc. on hand. The choices are not likely to change very often, if at all. One thing we definitely want to be able to do is set up data validation i.e. limit the choices for the data entry person to *only* these fixed options. Is that more readily accomplished using one or the other?

TIA,

Monte

Check constraints require DDL changes to adjust, tables just data modification.
If you want a list for the user, you have to duplicate your list in the check constraint at the front end.

My rule of thumb - I only use check constraints for formulae validations. Any lists I enforce with foreign keys & tables.

pootle flump ur codings are working excelent.

Changing the values in a table is a data change whereas changing a CHECK constraint is a schema change. If the set of values needs to change at runtime or is expected to change more often than you plan to release schema changes then you should use a table. Otherwise you can consider using a CHECK constraint.

In some cases a CHECK constraint can simplify the schema but it has the disadvantage that the list of values can't easily be queried by an application. That may be a problem for application developers but it depends on requirements.