Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008

    CHECK constraint vs. small table


    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?



  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    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.

  3. #3
    Join Date
    Dec 2007
    London, UK
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts