sql - 在 Firebird DB 中创建外键时出错

Question

我有 2 个表:commerciaux 和 production。我想在生产环境中创建一个链接到表 commerciaux 的外键。

ALTER TABLE PRODUCTION
ADD CONSTRAINT FK_PRODUCTION_1
FOREIGN KEY (NOM_COMMERCIAL)
REFERENCES COMMERCIAUX(NOM_COMMERCIAL)
ON DELETE CASCADE
ON UPDATE CASCADE

当我提交时我得到了错误:

This operation is not defined for system tables.
unsuccessful metadata update.
could not find UNIQUE or PRIMARY KEY constraint in table COMMERCIAUX with specified columns.

我该如何解决这个问题？

Answer 1

需求写在错误信息中

could not find UNIQUE or PRIMARY KEY constraint in table COMMERCIAUX with specified columns.

您必须在表 COMMERCIAUX 上创建这个或那个约束，以便列 COMMERCIAUX(NOM_COMMERCIAL) 将获得唯一索引。

参见章节 5.4.1.7.3 The UNIQUE Constraint

另请阅读另一章:

5.4.1.7.4 FOREIGN KEYA Foreign Key ensures that the participating column(s) can contain only values that also exist in the referenced column(s) in the master table. These referenced columns are often called target columns. They must be the primary key or a unique key in the target table. They need not have a NOT NULL constraint defined on them although, if they are the primary key, they will, of course, have that constraint.

如果没有唯一索引，Firebird 无法向您保证“目标列”中的值是唯一的。然后，如果不保证这些值是唯一的，Firebird 就无法实现多对一关系，即外键。