Geek Answers Handbook

Foreign key creation fails on existing table, but no orphan entries found

Using Firebird 2.1.

When refactoring a large system, I want to create a foreign key between tables that are already populated:

ALTER TABLE CMS_ARTRANS
ADD CONSTRAINT FK_ARTRANS_PRACTITIONER_ID
FOREIGN KEY (PRACTITIONER_ID)
REFERENCES CMS_SOLICITORS (RECID);

This message is not executed:

violation of the FOREIGN KEY constraint ". violation of the FOREIGN KEY constraint" PK_CMS_SOLICITORS "on the table" CMS_SOLICITORS ". The target link for the foreign key does not exist.

I somewhat expected that there would be problems with referential integrity, so I want FK in the first place. So I went looking for inappropriate entries:

SELECT
    *
FROM CMS_ARTRANS AR
LEFT OUTER JOIN CMS_SOLICITORS S
    ON (S.RECID = AR.PRACTITIONER_ID)
WHERE (AR.PRACTITIONER_ID IS NOT NULL) AND (S.RECID IS NULL)

And they are not. The set is NULL in CMS_ARTRANS.PRACTITIONER_ID. But there are no Non-NULLs that do not match the CMS_SOLICITOR entry.

Why doesn't Firebird like my FK?

+4
1

, , , : , , , . , , , , , , .

- ( ) gfix.

+5

More articles:


All Articles