Two-column Referential Integrity Constraint where one one colum value is a constant

Using some sample names here, we have a "Code" table that contains of a "CodeSystem" column and a "CodeValue" column, for which there is a unique (primary key) index. In table TestTable, we have a column TestColumn which should contain a value defined in one the CodeValue columns for a Code row with a fixed/constant CodeSystem value. That is, the column TestColumn should always contain a value from the 'FixedSystemName' CodeSystem. We have been unable to figure out how to define a referential integrity constraint to enforce this relationship.

Two of our previous attempts/quesses looked like:

ALTER TABLE TestTable
CONSTRAINT TestConstraint
Foreign Key ('FixedSystemName',TestColumn) References Code(CodeSystem,CodeValue);

and:

ALTER TABLE TestTable
ADD CONSTRAINT TestConstraint
CHECK (TestColumn IN (SELECT CodeValue FROM Code WHERE CodeSystem='FixedSystemName'));

Will we have to resort to a TRIGGER to perfrom this check or is there a way to define this as a standard referential integrity check?

What will be the preformance impacts of a TRIGGER check that looks like the second of the attempts we made above?

PART 2: If the Code table contained a unique row identifier (probably assigned from via a SEQUENCE) with its own unique index and the TestColumn value was linked to the Code table via such an identifier instead of the 2-column CodeSystem-CodeValue pair, would it be easier to define a valid referential integrity check? We could live with that solution too. Received on Thu Aug 23 2001 - 08:01:51 CDT