Create a Relationship in SQL Server 2017
Learn how to create a relationship between two tables in SQL Server.
In relational database design, a relationship is where two or more tables are linked together because they contain related data. This enables users to run queries for related data across multiple tables.
This part of the tutorial explains how to create the following relationships:
There are two relationships in that diagram. There's a relationship between the Albums and Artists tables, and there's another relationship between the Albums and Genres tables.
By looking at that diagram, we can see that one artist could have many albums. In this case, we only need to have one row that contains the artist's name, regardless of how many albums they have. This is because we can create a record in the Artists table with a unique ArtistId. All albums for that artist will reside in the Albums table, and they will contain the same artist ID in its own ArtistId column. By doing this, we can run a query across both tables and return the artist's name, plus all the albums they've released. This is the benefit of relationships.
Previously we created a database containing the above three tables. When we did that, we also created one of the relationships depicted in the above diagram. We created the relationship between the Albums table and the Artists table (where the ArtistId column of the Albums table references the ArtistsId column of the Artists table).
Here's the code we ran to create the tables:
The highlighted code is the part that creates a relationship between the Albums table and the Artists table. It does this by setting the ArtistId column of the Albums to reference the ArtistId column of the Artists table.
A more technical way of saying this is that Albums.ArtistId becomes a foreign key of Artists.ArtistId (which itself is the primary key of that table). This is a foreign key constraint.
What's a Foreign Key Constraint?
A foreign key constraint defines a relationship between this table and another table. When you create a foreign key constraint, you create it against a specific column in the child table, to reference a specific column in parent table.
This makes that column in the child table a foreign key. The constraint ensures that any value that goes into this (foreign key) column corresponds with a value in the primary key column of the parent table. If someone tries to enter a value that doesn't correspond with a value in the parent table's primary key column, SQL Server will throw an error.
This helps enforce referential integrity. It prevents us from having orphaned records (child records that have no parent). Or in our example, albums that aren't associated with any artist.
If you use a GUI database management tool like SSMS or Azure Data Studio, the relationship will appear under the Keys node for the table with the foreign key:
Let's deconstruct the code:
The first two lines create the relationship. They create a foreign key constraint between the Albums.ArtistId column and the Artists.ArtistId column. In this case we call the foreign key constraint FK_Albums_Artists.
The last two lines specify what SQL Server should do if someone tries to delete or update a parent record that is being referenced by a record in the child table. In this case, NO ACTION means that the delete/update won't go ahead. The user will just get an error.
You could change this to ON DELETE CASCADE if you want to be able to delete the parent and the child in one go (i.e. the delete will cascade from the parent to the child). The same logic applies to updates, by using ON UPDATE CASADE.
NO ACTION is the default value, so we could've done without those last two lines of code. However, I included it, because it's an important factor to think about when creating foreign key constraints.
Add a Relationship to an Existing Table
The previous example creates a relationship at the same time the tables are created. However, there may be times when you need to add a relationship to an existing table.
Let's add a new relationship between the Genres and Albums tables.
Run the following code:
This creates a new foreign key on the Albums table. It results in Albums.GenreId becoming a foreign key that references Genres.GenreId.
So running that statement results in a new foreign key being displayed under the Keys node:
Single Column Foreign Keys
Single column foreign keys (like the one above) can also be specified at the column level. So another way of creating the Albums table and its foreign key is like this:
This method can't be used on multicolumn key constraints, so for those, use the syntax in the original example above.
Multicolumn Foreign Keys
A multicolumn foreign key is where more than one column is used for the foreign key. This is typically used when the parent table uses multiple columns for its primary key. This might be the case if the parent table combines the values from two columns to create a unique value.
Multicolumn foreign keys can be created using the syntax in the original example above. Just add each column name separated by a comma.
So if we imagine that the Albums table also has an ArtistName column (and that the Artists table uses ArtistId and ArtistName as its primary key), a multicolumn foreign key would look like this: