On Sat, Jan 11, 2003 at 14:00:14 -0800,
Josh Berkus <josh@agliodbs.com> wrote:
>
> This should give you a system in which animal_id is the primary key for
> each table, and therefore there is a one-for-one relationship between
> the animal table and each of the dog and cat tables, and would prevent
> you from deleting a referenced record from the animal table.
>
> You would need an additional trigger to prevent duplication *between*
> the dog and cat tables.
There is a way to do this without triggers. You add an animal type
field to all of the tables. The foreign keys then should use both animal id
and animal type (which means you need to make animal_id and animal_type
a combined unique key on the animal table). You add a constraint to the animal
specific tables forcing the animal type to be the appropiate type.