Foreign key confusion

[Marcin Bury]

Hi

I'd like to add foreign key to a table that already has lots of records
using:

ALTER TABLE LOGIT_WLO
ADD CONSTRAINT FK_LOGIT_WLO_ROZLADUNEK
FOREIGN KEY (ROZLADUNEK_ID)
REFERENCES MGP_ROZLADUNEK(ID)
ON DELETE SET NULL
ON UPDATE CASCADE



The field ROZLADUNEK_ID has values from referenced MGP_ROZLADUNEK ID
field or null. Both fields are integer ones.
So it seems to be ok

However I'm getting following error:

violation of FOREIGN KEY constraint "".
violation of FOREIGN KEY constraint "FK_LOGIT_WLO_ROZLADUNEK" on table
"LOGIT_WLO".
Foreign key reference target does not exist.
Problematic key value is ("ROZLADUNEK_ID" = NULL).
-----------------------------------------------------------------------------------
SQLCODE: -530
SQLSTATE: 23000
GDSCODE: 335544466

I supposed that null value is allowed in foreign keys, so what might be
the source of this error?

TIA
Marcin

[Mark Rotteveel]

This should work just fine. Which Firebird version? Are you sure that
`ROZLADUNEK_ID` doesn't have the literal value 'NULL' (as opposed to NULL).

A fully reproducible example would be helpful.

Mark
--
Mark Rotteveel

[Marcin Bury]

------ Wiadomość oryginalna ------
Od: "'Mark Rotteveel' via firebird-support"
<firebird...@googlegroups.com>
Do: firebird...@googlegroups.com
Data: 24.04.2024 11:28:40
Temat: Re: [firebird-support] Foreign key confusion

Firebird is WI-V6.3.7.33374 Firebird 3.0, should provide this in the
first place, sorry... ;-)
As said before - both fields are integer so I assume they cannot have
literal value 'NULL'. IBObjects based application and IB Expert indicate
nulls as well,

Marcin

>

[Pieter Bas Hofstede]

Could is be that the column-definition (domain) has had different versions during its lifetime? In other words, can you try backup+restore cycle and after that add the FK?

Op woensdag 24 april 2024 om 11:59:08 UTC+2 schreef marci...@studio-delfi.pl:

[Tim Crawford]

Just came across same issue (Firebird 3)

Did a backup and restored then it worked so upvote that answer