4

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64
zychin
  • 65
  • 5
  • 1
    What about two left joins, one on each subtype tables. – Shadow Nov 27 '20 at 16:50
  • @Shadow Two left joins could work, as it would check the id for both subtypes. If I were to use that implementation, I will have to change the id for dog and cat, to be dog_id and cat_id, and check for the column that is not null. – zychin Nov 27 '20 at 17:05

1 Answers1

5

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • That's an interesting idea. For this implementation, I presume both animal_id and animal_type are required to determine the appropriate row and table of the animal. – zychin Nov 27 '20 at 17:22
  • Right, after you query a row from `animal` then you know which table to query for the animal-specific attributes. – Bill Karwin Nov 27 '20 at 17:26
  • this might be a more bulletproof answer https://stackoverflow.com/a/17122961/1201238 – No_name Apr 23 '22 at 09:26