I have 2 tables: commerciaux and production I want to create a Foreign key in production linked to the table commerciaux

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

When I commit I got the error :

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.

How can I fix this issue?

CodePudding user response：

The requirement is written right in the error message

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

You have to create this or that contraint on the table COMMERCIAUX, so that the column COMMERCIAUX(NOM_COMMERCIAL) would get unique index.

See chapter 5.4.1.7.3 The UNIQUE Constraint

Also read there another chapter:

5.4.1.7.4 FOREIGN KEY A 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.

Without having unique index Firebird can not give you warranties that value in "target columns" are unique. Then, without warranty of those values being unique Firebird can not materialize many-to-one relationship, that Foreign Key is.