On Thu, 2003-04-24 at 00:28, Stephan Szabo wrote:
>
> On 24 Apr 2003, Hadley Willan wrote:
>
> > Thanks. It's pretty much what we'd thought then. But sometimes you like
> > to check these things out to avoid the "doh!" that comes later.
>
> Well, one other workaround is to make a table that has only the ids and
> then have each of unit, boats, cars, etc... foreign key an id to that.
> Then at least you can foreign key to the id table, but that doesn't help
> you with the allowing dups between boats and cars.
>
variation on the theme... make one table that holds all of the ids, with
a unique constraint on that tables id column. then write a trigger
function on each "child" table to insert the id's into the "parent"
table. if your child table tries to insert an existing id it will error
out and not allow the insert. as preventative medicine, use 1 sequence
to drive all of the tables, and set the default id to
nextval(common_seq); that should help keep you from getting duplicates
in the first place.
Robert Treat