If you’re new to relational databases, and you’re trying to get your head around this concept of a “relationship” in your database, I hope this article helps.
What is a Database Relationship?
When it comes to databases, a relationship is where two or more tables contain related data, and you’ve configured your database to recognise (and enforce) that fact.
The reason you would do this is to ensure that the integrity of the data is not compromised.
Consider the following database diagram.
The dashed lines represent a relationship between the tables.
I have a relationship between the Artists and Albums tables because the Albums table contains the ArtistId (so that I know which artist released each album). The reason I established this relationship is because I don’t want anyone to enter a non-existent ArtistId. That is, I don’t want the Albums table to contain an ArtistId that doesn’t exist in the Artists table.
You can see that I’ve also established a relationship between the Genres table and the Albums table. And I’ve done this for the same reason – I don’t want the Albums table to contain a GenreId that doesn’t exist in the Genres table. If an album is from a genre that doesn’t exist in the database, someone will need to first enter that genre into the Genres table. Once they’ve done that, they can go ahead and enter the album from that genre.
Types of Relationships
You’ll often hear of 3 types of relationships that can be applied to a relational database. Here’s an overview of each one.
One-to-One
A one-to-one relationship is a relationship between two tables where each table can have only one matching row in the other table.
Using the above screenshot as an example, the business case is that each employee’s pay details must be stored in a separate table to the employee’s contact details. In such a case, there can only be one row in the Pay table that matches a given employee in the Employees table. This is a good candidate for a one-to-one relationship.
One-to-Many
The one-to-many relationship is similar to the one-to-one relationship, except that it allows multiple matching rows in one of the tables.
In the above example, each author can have many books, but each book can only have one author.
Therefore, the Books table is allowed to contain multiple rows with the same AuthorId value. If an author has released five books, then there would be one row in Authors for that author, and five rows in Books, each with that author’s AuthorId.
Many-to-Many
In a many-to-many relationship, each side of the relationship can contain multiple rows.
In this example, each book is allowed to have multiple authors. Therefore, I created a lookup table (also known as a “junction table”) that stores both the AuthorId and the BookId.
These two columns could be configured to be the primary key of the table (in which case they would be a “composite primary key” or simply “composite key”), or you could create a separate column to be the primary key.
Note that the Books table doesn’t have AuthorId in this case. That column has been moved to the AuthorBooks table so that we can have many AuthorIds for the same BookId.
Creating a Database Relationship
Database relationships can be created via a GUI tool (such as SQL Server Management Studio, Microsoft Access, etc) or by running an SQL statement.
Examples:
When creating a relationship with SQL, you do it by applying a foreign key constraint against the child table. This foreign key references the primary key in the parent table.
Examples