SQL Server 2016: Create a Relationship
You can create a relationship between tables by using the GUI or SQL script. Here, I demonstrate both methods.
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.
Here, we will create the following relationships.
Here's how we'll do it:
- We'll use SQL to create the Albums table and one relationship.
- We'll use the GUI to create the other relationship.
That way, you'll get to see both methods of creating a relationship.
We only need to create one table because we've already created two of these tables previously in this tutorial (the Artists table via the GUI and the Genres table using SQL).
Create a Relationship using SQL
Open a new query window in SSMS and run the following code:
The first part of that statement creates the table.
The last part defines the relationship. This part:
The first two lines create the relationship. They create a foreign key constraint between the Albums.ArtistId column and the Artists.ArtistId column.
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.
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 the 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.
Create a Relationship via the GUI
Now we'll create the other relationship via the SQL Server Mangement Studio's GUI.
It would've been easier to include this in the above script but I wanted to demonstrate both methods of creating a relationship.
Open the Child Table in the Table Designer
Open the Foreign Key Relationships Dialog
Add the Relationship
Select Tables And Columns Specification
The Tables and Columns Dialog Box
Saving The Relationship