Duplicate check constraint on multiple tables?

Question

I have an integer value for a data to be included into two or three tables, Say it is part no. The integer value of part no must be between 1 to 8. I want to add that constraint into the tables.

What method should I follow? Should I create constraint on each of the tables or just create one table with the values for part no and reference the table from others?

Answer 1

I would create another table.

This allows for several possibilities:

1. that one day some other part number such as 9 would be acceptable. You can quickly and transparently add it to the table, rather than changing a bunch of constraints on different tables.
2. that you might want to store some extra data alongside each part number, for example the name of the part. You can add a column to the table which you already have.

The only situation where I would not do it this way, was if 1 to 8 is something which could never ever change. For example, you might be sure that there will only ever by 7 days of the week (but who knows?).

In this case I would create a TYPE with this constraint. Then you can use the type in multiple tables. If the days of the week (or whatever) DOES change, you can change the TYPE.