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



Ah, but getting a bit off topic here...


There's many solutions for each problem ;-)

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