Re: foreign key, or "in" constraint?

On Oct 6, 4:39 pm, m..._at_pixar.com wrote:
> Suppose you have a table with a status column, and
> the status can be one of "queued", "inprogress",
> "failed", or "completed".
>
> This can be enforced two ways:
>
> 1.  status has a FK to a "statuses" table, and the allowed statuses
>     are in that table.
>
> 2.  with a constraint such as
>         status in ('queued','ip','failed','completed')
>
> Which is more typically used?  What are the pros and cons,
> tradeoffs, etc, of each?
>
> Many TIA!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

The pros of the column constraint approach is you do not need a second table.

The cons of the column constraint approach is that the values are basically hard coded and if you need to apply the same restriction to another table you now have two or more places that have to be changed in the event you add or remove another value.

I think the key questions are: How likely are the values to every change and on how many different tables would you need to actually check that the value is valid? The closer the anwers are to never and one the more applicable the column constraint is.

HTH -- Mark D Powell -- Received on Mon Oct 06 2008 - 17:22:41 CDT