4

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?

Marian Aldenhövel
  • 677
  • 1
  • 6
  • 24
  • I'm not sure about your particular DBMS, but you may have to explicitly state that an FKey can be `NULL` and still be valid. See: http://stackoverflow.com/questions/2366854/can-table-columns-with-a-foreign-key-be-null – aruisdante Nov 03 '14 at 16:23
  • Is there an existing index on `PRACTITIONER_ID`? This could happen with a corrupted index: the query uses the index and doesn't see certain records, while actually creating the foreign key does see them. You could verify this by either dropping the index or - assuming this is an integer field - replacing **both** occurrences of `AR.PRACTITIONER_ID` with `AR.PRACTITIONER_ID+0` (this will force the query to not use an index). – Mark Rotteveel Nov 03 '14 at 18:05
  • I have had this happen shortly after correcting the values in my foreign key field. Waiting some time (letting the transaction become older than oldest interesting transaction) and sweeping the database with gfix -sweep seemed to fix it. – nater Nov 03 '14 at 19:12

1 Answers1

7

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.

jonneve
  • 555
  • 4
  • 16
  • Yes, I think that is the cause of my problem. I had not thought of concurrent users/transactions as it happened on a development-copy of the DB and I expected to be the sole user of it. I now suspect blocking myself by holding open "invisible transactions" in my DB-frontend-tool (IBExpert). Restarting the DB-server-service as most brutal measure and then retrying the ADD CONSTRAINT fixed the problem. So as usual: Embarassingly simple. Thanks everybody. – Marian Aldenhövel Nov 04 '14 at 14:56