At 08:28 PM 11/04/2006, you 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.
> >
> > 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? :-)

If you want monogamy, put unique constraints on HusbandID and WifeID. ;-)

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

I wouldn't. I don't like having the PK getting jumbled up with the
foreign keys. Artificial PKs are cheap and are good from an indexing POV.

./heLen