Determine type from Class Table Inheritance

Question

I am trying to implement a database that has a table structure similar to below, where 2 tables are a subtype of a table.

An animal has a primary key, and dog and cat has a foreign key that references animal_id of animal.

animal(animal_id, bornAt)
dog(animal_id, barkSound)
cat(animal_id, meowSound)

It is worth noting that dog and cat are disjoint, so it is not possible for them to reference the same animal_id in animal.

Is there a way to use SQL code to determine the type of the animal, given the animal_id? For example, if there is a dog with animal_id of 4(not knowing the fact that animal_id 4 is dog), I would like to retrieve the data with a join from the animal and dog table.

Answer 1

One way I have seen to enforce the disjoint types is to define an animal type column in the animal table, and make your foreign keys reference it.

animal(animal_id, animal_type, bornAt)
dog(animal_id, animal_type, barkSound)
cat(animal_id, animal_type, meowSound)

Suppose dog.animal_type is constrained to only 'dog', and cat.animal_type is constraint to only 'cat'. Then the foreign key from dog and cat referencing animal uses the pair of columns referencing a compound unique key in animal.

That way you get enforcement of your disjoint types, because a given row in animal must either have 'dog' or 'cat' but not both. Also you would know the animal type of a given row in the animal table without having to join to other tables to see if there's a match.