foreign key, or "in" constraint?

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

On Oct 6, 4:39 pm, m... RemoveThis @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 --