So a variation of this question has been asked many times; in all of those cases, usually the recommendation is a modifying of the data and the suggested rewrites often make more sense but I can’t figure out a way, with my specific example.

Basically, all elements are of a shared type. So there’s a single, shared table; say, Animals with the columns animal_id, animal_name, animal_type, number_of_legs, etc. You get the idea.

The data is coming in from elsewhere so, while there’s a few types we can expect, that’s not the case all over.

So we might have a Mammals table and a Reptiles table. Receive a type of animal which I haven’t accounted for? No worries; the animal_type column (which is a varchar) will capture that info. If I decide I want to account for that type of animal in a designated way or it has data that the other animals don’t, I can extend my database and add a table for it. Mammals would have a mammal_id primary key which is a foreign key to animal_id; same deal with Reptiles and reptile_id, etc.

And each table can have further subtypes; so animal_types of Dogs, Cats, etc. could end up in Mammals and I can tell the difference based on the animal_type listed in Animals.

So say Mammals has a field like owner or something; but owner is dependent on the animal_type. So people can’t own more than one Cat but can own a Cat and a Dog.

If animal_type were in Mammals, this would be trivial: unique constraint on animal_type and owner. But animal_type applies to every Animal so it wouldn’t make sense in only that table. I could put animal_type in both Animals and Mammals but this is duplicating and replicating data in multiple tables with no assurance of syncing.

I may just not be knowledgeable enough in SQL, yet, but what’s the best way to enforce that no owner has an Animal of more than one of one animal_type?