Insert values into tables with a foreign key

Question

I want to insert some values in two tables in a loop, but it seems like I can't do it because of the foreign key. However, the same code does work in SQL Server.

This works perfectly in SQL Server:

declare @i int = 1

while @i<1200
BEGIN

INSERT INTO DOKUMENTY VALUES(null,null);
INSERT INTO POZYCJE VALUES
( null,@i,RAND()*(10000) ) ,
( null,@i,RAND()*(10000) ),
( null,@i,RAND()*(10000) ),
( null,@i,RAND()*(10000) )

SET @i+=1

END

I'm trying to do the same in Firebird:

SET TERM #;
execute block 
as
declare variable cnt2 integer = 0;
begin
    while (cnt2 < 1200) do
    begin
        insert into DOKUMENTY(DATA_KSIEGOWANIA) values(current_date);

        insert INTO POZYCJE(ID_DOKUMENTU, KWOTA)
        select (:cnt2), MOD(RAND(),1000) FROM RDB$DATABASE UNION ALL
        select (:cnt2), MOD(RAND(),1000) FROM RDB$DATABASE UNION ALL
        select (:cnt2), MOD(RAND(),1000) FROM RDB$DATABASE UNION ALL
        select (:cnt2), MOD(RAND(),1000) FROM RDB$DATABASE ;
        cnt2 = cnt2 + 1;
    end
end#

SET TERM;#

However, I'm getting this error:

Error: *** IBPP::SQLException ***
Message: isc_dsql_execute2 failed

SQL Message : -530
can't format message 13:470 -- message file C:\WINDOWS\SYSTEM32\firebird.msg not found

Engine Code    : 335544466
Engine Message :
violation of FOREIGN KEY constraint "INTEG_44" on table "POZYCJE"
Foreign key reference target does not exist
Problematic key value is ("ID_DOKUMENTU" = 0)
At block line: 10, col: 9

This is how I create the tables:

CREATE TABLE DOKUMENTY(
ID_DOKUMENTU integer generated by default as identity primary key,
DATA_KSIEGOWANIA DATE
);

CREATE TABLE POZYCJE(
ID_POZYCJI integer generated by default as identity primary key,
ID_DOKUMENTU integer,
FOREIGN KEY(ID_DOKUMENTU) references DOKUMENTY(ID_DOKUMENTU),
KWOTA decimal(18,2)
);

How do I fix this?

Mark Rotteveel · Accepted Answer · 2021-03-13T10:06:53.280

Your Firebird code is not equivalent to your SQL Server code. Your SQL Server code starts at 1, while your Firebird code starts at 0. The first value generated for an identity column is 1, not 0, so your insert fails the foreign key constraint because there is no record in DOKUMENTY with ID_DOKUMENTU = 0. So, the immediate solution is to use declare variable cnt2 integer = 1; instead of declare variable cnt2 integer = 0;

However, your code is currently relying on the identity column actually starting at a specific value (1), and always incrementing by 1. That is not a safe solution, for example, if you try to run it now with this change, it will fail because the insert of the record with ID_DOKUMENTU = 1 was undone when your execute block failed, and the next record inserted will have a higher id.

Instead, modify your code to use the actual generated id:

execute block 
as
declare variable cnt2 integer = 1;
declare variable ID_DOKUMENTU type of column DOKUMENTY.ID_DOKUMENTU;
begin
    while (cnt2 < 1200) do
    begin
        insert into DOKUMENTY(DATA_KSIEGOWANIA) values(current_date) 
           returning ID_DOKUMENTU into ID_DOKUMENTU;

        insert INTO POZYCJE(ID_DOKUMENTU, KWOTA)
        select (:ID_DOKUMENTU), MOD(RAND(),1000) FROM RDB$DATABASE UNION ALL
        select (:ID_DOKUMENTU), MOD(RAND(),1000) FROM RDB$DATABASE UNION ALL
        select (:ID_DOKUMENTU), MOD(RAND(),1000) FROM RDB$DATABASE UNION ALL
        select (:ID_DOKUMENTU), MOD(RAND(),1000) FROM RDB$DATABASE ;
        cnt2 = cnt2 + 1;
    end
end

As an aside, MOD(RAND(),1000) does not do the same thing as RAND()*(10000) in your SQL Server code.

Insert values into tables with a foreign key

1 Answers1