Inserting rows with cyclic referential dependencies

Christof Lutterot wrote:

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?

Helen Borrie answers:

Don't try to resolve a circular reference within the table itself. Create one table for the Entities and another for the partnerships - a "left" and a "right" table.

I can't make sense of your curious column names so here's a simple example:

create table Person (
   id integer not null,
   FirstNames varchar(60),
   Surname varchar(60) not null,
   .... other data...,
   constraint PKPerson primary key(id)
);
commit;
create table Partnership (
   Partnership_id integer not null,
   HusbandID integer not null,
   WifeID integer not null
     check (WifeID <> HusbandID),
   constraint PKPartnership primary key(Partnership_id),
   constraint FKHusband foreign key (HusbandID) references Person,
   constraint FKWife foreign key (WifeID) references Person,
   constraint UQPartnership Unique(HusbandID, WifeID)
);
commit;