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 table1
    ADD CONSTRAINT foo CHECK (field1 || field2 IN (SEL开发者_如何学编程ECT fieldx FROM table2));

ALTER TABLE table1
    ADD CONSTRAINT foo FOREIGN KEY (field1 || field2) REFERENCES table2 (fieldx);

Is this possible? If yes, how? Beside this, is it generally possible to use subselects in CHECK Constraints?

I'm using DB2/LINUX 9.5.0.

Solution:

It seems to be only possible with an extra column, that holds the combinied value. The good thing is, Tony Andrews pointed out, there is a way to create expression generated columns in DB2.

CREATE TABLE table1 (
    field1 CHARACTER(5),
    field2 CHARACTER(5),
    fieldx CHARACTER(10) GENERATED ALWAYS AS (field1) || field2),
    CONSTRAINT FK_X FOREIGN KEY (fieldx) REFERENCES table2 (fieldx)

);

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:

ALTER TABLE table1
    ADD CONSTRAINT 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).

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:

ALTER TABLE table1
   ADD FOREIGN KEY (fk1)
     REFERENCES table2 (key1) ON DELETE RESTRICT

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

ALTER TABLE table1
   ADD FOREIGN KEY (fk1,fk2)
     REFERENCES table2 (key1,key2) ON DELETE RESTRICT

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.