如何对两个组合字段施加限制？ [英] How to put a constraint on two combined fields?

问题描述

我想在表2中的table1到另一个字段的两个组合字段上放置一个约束，一个检查或一个外键。这是我试过的，但都给了我错误：

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 (SELECT fieldx FROM table2));

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

这可能吗？如果是，怎么样？除此之外，通常可以在CHECK约束中使用子选项吗？

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

我正在使用DB2 / LINUX 9.5.0。

I'm using DB2/LINUX 9.5.0.

解决方案：

似乎只有一个额外的列才能保存组合的值。好的是， Tony Andrews 指出，有一种在DB2中创建表达式生成列的方法。 p>

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)

）;

推荐答案

一种可能性是在table1上保留一个计算列

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

fieldx = (field1 || field2)

我不知道DB2是否支持计算（也就是虚拟）列，但如果不是，您可以创建一个常规列并通过触发器来维护它。创建外键约束：

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);

当然，另一种可能性是修改桌面设计，使键保持一致：如果field1和field2是原子值，那么它们应该在表2中显示，而不是连接的值（或多或少打破1NF）。

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).

这篇关于如何对两个组合字段施加限制？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！