RDBMS includes the word relational, indicating that the objects in our database should relate to one another. When we want to relate tables, at least one column of the same information should be included in the tables we want to relate. The primary reason I chose the Cat, Dog, and owner example is because it helps to illustrate the relationship in a clear way. A PetOwner can have a dog, but a dog may not necessarily have an owner. When a dog does have an owner, there is a hierarchy—the owner owns the dog. We can use this hierarchy to then establish a relationship. When a dog has an owner, the owner can then be used to help describe the dog.
In our database, an owner can have zero dogs, one dog, or many dogs. We are using the OwnerIdentifier column to identify an owner in our database. When we add the OwnerIdentifier column to the Dog table—to help describe the dog–we can create a foreign key in the Dog table. A foreign key is a column or combination of columns that is used to establish and enforce a link between the data in two tables. This allows us to create a relationship between our tables. This relationship is shown by the line between the two tables. This relationship helps the RDBMS know about the relationship.
**Foreign Key Image**
Each of these tables has a column named OwnerIdentifier and a foreign key has been created on the Dog table. Both columns are the same data type. While you can join on columns of different data types, the best practice is to make the column data types the same.
**Warning** While two tables have columns with the same name, this does not create the foreign key relationship. A foreign key must be created when the relating table (in this case, Dog) is created or can be created afterwards. If the database diagram does not show the relationship with a line, the foreign key has not been created.
Now that we know the columns that each table shares in common, we can join them; however, the type of join we use will depend on the question we are trying to ask. For example, do we want to find only people with cats that don’t have a dog? Do we want to find only people that have a dog and no cats? Do we want to find people that have at least one cat and one dog? Answering this question will then tell us which type of join to use.
Want to follow along on your own database?
Do you want to follow along with the examples? We have created a sample database you can use to follow along and try in your own database. Go to the download page and follow the instructions. Have fun on the SQL trail!