Hi,

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

Not with Firebird. What you are referring to, is called "deferred
constraints".
Firebird only supports "immediate constraints".

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

No.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com