Foreign key confusion

71 views
Skip to first unread message

Marcin Bury

unread,
Apr 24, 2024, 3:58:06 AM4/24/24
to firebird...@googlegroups.com
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

unread,
Apr 24, 2024, 5:28:47 AM4/24/24
to firebird...@googlegroups.com
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

unread,
Apr 24, 2024, 5:59:08 AM4/24/24
to firebird...@googlegroups.com


------ 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

unread,
Apr 24, 2024, 7:37:32 AM4/24/24
to firebird-support
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

unread,
Dec 5, 2025, 11:52:14 AM (11 days ago) Dec 5
to firebird-support
Just came across same issue (Firebird 3)
Did a backup and restored then it worked so upvote that answer

Reply all
Reply to author
Forward
0 new messages