Hello!

I am using Firebird 1.5.2.4854 and encounter a problem when inserting
rows into a database by using the SQL script execution function of the
Flamerobin 0.6 tool.

I have a table

CREATE TABLE EntityRole
(
...
Partner INTEGER NOT NULL,
...
CONSTRAINT EntityRole_PartnerFK FOREIGN KEY (Partner)
REFERENCES EntityRole(Id)
ON DELETE CASCADE,
...
);

and want to insert

INSERT INTO EntityRole (Id, Name, Owner, PartnerOwner, Partner,
MinMult, MaxMult, FKTableName, FKColumnName, IsFrozen)
VALUES (0, 'name', 0, 50, 1, 1, 1, null, null, 'T');

INSERT INTO EntityRole (Id, Name, Owner, PartnerOwner, Partner,
MinMult, MaxMult, FKTableName, FKColumnName, IsFrozen)
VALUES (1, null, 50, 0, 0, 0, 1, null, null, 'T');

So I am referencing the second row in the first row that I am trying
to insert, and the first in the second, i.e. a cycle.
What happens is:

*** IBPP::SQLException ***
Context: Statement::Execute( INSERT INTO EntityRole (Id, Name, Owner,
PartnerOwner, Partner, MinMult, MaxMult, FKTableName, FKColumnName,
IsFrozen)
VALUES (0, 'name', 0, 50, 1, 1, 1, null, null, 'T') )
Message: isc_dsql_execute2 failed

SQL Message : -530
violation of FOREIGN KEY constraint ""

Engine Code : 335544466
Engine Message :
violation of FOREIGN KEY constraint "ENTITYROLE_PARTNERFK" on table
"ENTITYROLE"


As far as I can see, Flamerobin executes the two inserts in the same
transaction. Isn't it that constraints like ENTITYROLE_PARTNERFK have
to be valid before and after, but not during transactions? Why does
this error occur and how can I fix this?
One possible solution would probably be to insert the rows with null
values in column Partner and then updating these values accordingly,
but isn't there a simpler way to make Firebird check the constraints
only after but not during the transaction?

Thanks for any help,
Christof