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

>
> Did I say "want"? ;-)
>
> > > > 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.

>
> Depends on the situation... Given that these are single column FKs,

which

> are unique anyway, the index should be quite ok.

I agree with Helen. IMO there is almost no exception to the use of
primary keys with no business rule significance (ie artifically
generated keys). It makes it possible to create a foreign key
relationship which is quite tricky to achieve if you have a compound
primary key. For example, you may create a foreign key link from a new
parent-children table to the relationship table and another foreign
key link in that new table back to the person table.

Adam