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

Simple? :-)

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

Ah, so you can have multiple Husband-Wife combinations? :-)

> constraint FKHusband foreign key (HusbandID) references Person,
> constraint FKWife foreign key (WifeID) references Person,
> constraint UQPartnership Unique(HusbandID, WifeID)
> );

I would do a PK on (HusbandID, WifeID) and drop the artifial
PK in this one ... :-)

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com