Creation of foreign key fails on existing table, but no orphan records found

Question

Using Firebird 2.1.

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

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

This fails with the message:

violation of FOREIGN KEY constraint "". violation of FOREIGN KEY constraint "PK_CMS_SOLICITORS" on table "CMS_SOLICITORS". Foreign key reference target does not exist.

I somewhat expected there to be problems with referential integrity, which is why I want the FK in the first place. So I went looking for non-matching records:

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 there are none. Plenty of NULLs in CMS_ARTRANS.PRACTITIONER_ID. But no Non-NULLs that do not match a CMS_SOLICITOR-record.

Why is Firebird not liking my FK?

Answer 1

Most commonly, this happens when there are records visible to other transactions which do not satisfy the primary key: perhaps you deleted all problematic records for example, but they are still visible to other transactions. So the solution is either to wait for transactions older than your's to get closed, or force them to close.

In practice, the easiest way is to take the database offline (if you can afford to) using gfix.