How To Put A Constraint On Two Combined Fields?

I'd like to put a constraint, a check or a foreign key, on two combined fields from table1 to another field in table2. Here is what I tried, but both gave me errors: ALTER TABLE ta

Solution 1:

One possibility would be to hold a computed column on table1 i.e.

fieldx = (field1 || field2)

I don't know if DB2 supports computed (aka virtual) columns as such, but if not you can create a regular column and maintain it via a trigger. The create the foreign key constraint:

ALTERTABLE table1
    ADDCONSTRAINT foo FOREIGN KEY (fieldx) REFERENCES table2 (fieldx);

Another possibility, of course, would be to modify your table design so that the keys are held consistently: if field1 and field2 are atomic values, then they should appear as such in table2, not as a concatenated value (which more or less breaks 1NF).

Solution 2:

You don't, the foreign key must have the same number of columns as the parent key, also consider that keys need indexes, so consider them as "look-up".

For one FK to one PK:

ALTERTABLE table1
   ADDFOREIGN KEY (fk1)
     REFERENCES table2 (key1) ONDELETE RESTRICT

If a key on table 2 is composite (key1, key2)

ALTERTABLE table1
   ADDFOREIGN KEY (fk1,fk2)
     REFERENCES table2 (key1,key2) ONDELETE RESTRICT

Solution 3:

Try adding 3 constraints: 2 nullable foreign keys for field1 and field2, and a constraint that only one of two is not null.

Of course, you can relax the constraints and omit the last one.