Hello,

it's rather a standard SQL or best practices question I have:

Given a column "name" in a table

CREATE TABLE Prepositions
(
name VARCHAR(4) PRIMARY KEY CHECK (name IN ('', 'of', 'to', 'in',
'for', 'on', 'with', 'as', 'by', 'at', 'from'))
)

and using it (as an identifying relationship) from another table by
referencing it via foreign key from another table does not require
repeating the CHECK constraint there, like

CREATE TABLE Actions
(
name VARCHAR(20),
preposition_name VARCHAR(4) CHECK (preposition_name IN ('', 'of',
'to', 'in', 'for', 'on', 'with', 'as', 'by', 'at', 'from')) REFERENCES
Prepositions (name),
PRIMARY KEY (name, preposition_name)
)

right? I haven't seen any code like this, so I suspect this is not
possible and thus not applicable. I'm jmust not 100% sure.

Can anyone confirm this please?

In case: Is the second check not needed because the foreign key
constraint will fail if the check fails on the other table?

Karsten

Karsten Wutzke wrote on 01.04.2010 23:30:
The second check is not needed by design. actions.preposition_names can only take values that are present in the table prepositions and thus by definition can only have values that are covered by the check constraint.

Thomas