Understanding Table Relations
SQL server adds foreign keys to implement relations between tables in a relational database. A table can have one-to-one, one-to-many, or many-to-many relations with another table, depending upon the type of data you want to store.
A one-to-one relationship is simple and rarely used. Each record in a particular table relates to exactly one record in another table.
For example, you can store the user’s name, id, and gender in one table (User table) and their addresses will be stored in the different table (Address table). Each record in the User table will correspond to one record in the Address table. Conversely, each address in the Address table will belong to only one record in the User table.
One-to-many and many-to-many relations are far more frequent.
Consider a scenario where you store information about an organization’s employees, departments, employees’ insurance companies, and employees’ office addresses.
We assume that an employee can belong to a single department only and can subscribe to only one insurance company. However, departments and insurance companies can have multiple employees. The Department and Employee tables would have a one-to-many relationship. Similarly, the Insurance and Employee tables would have a one-to-many relationship.
Also, the organization may have multiple offices, and an employee may work at more than one office. Besides, an office can have multiple employees. In this case, the Office and Employee tables would have a many-to-many relationship.
To implement the many-to-many relationship, you need to create a look-up table that connects two tables in a many-to-many relationship. The original tables contain a one-to-many relationship with the look-up table.
Let’s consider creating a many-to-many relationship between the Employee and Office tables. You would create a new look-up table Employee_Office. Then you create a one-to-many relationship between the Employee and Employee_Office tables and the Office and Employee_Office tables.
Let’s now see how foreign keys fit in this discussion.
What are Foreign Keys and Why you Need Multiple Foreign Keys?
In relational databases, foreign keys implement different types of database relationships.
For example, to implement a one-to-many relationship in SQL Server, you need to add a foreign key in a table that is on the many sides of the one-to-many relationship.
The foreign key references the primary key or the unique identity key of the table that is on one side of the table. Hence, in a one-to-many relationship between the Department and Employee tables we discussed above, the Employee table will store a foreign key that references the primary key of the Department table.
In scenarios where a table can have relationships with multiple other tables, you will need to add multiple foreign keys to a table. For the Employee table, you need to add foreign keys that reference the primary keys of the Department table and the Insurance table. Similarly, the Employee_Office lookup table will have two foreign keys that reference the primary keys of the Employee and Office tables.
Adding Multiple Foreign Keys with SQL Server
The following diagram shows the database schema that you are going to implement for our simple example:
Here you can see that the database has five tables: Employee, Insurance, Department, Office, and Employee_Office.
The Department and Employee tables have a one-to-many relationship. Similarly, the Insurance and Employee tables also have a one-to-many relationship. The Office and Employee tables have a many-to-many relationship implemented using two one-to-many relationships with the Employee_Office lookup table.
The Employee table has two foreign keys, Dep_Id, and Insur_Id which refer to the primary keys (Id) of the Department and Insurance tables, respectively.
Let’s now execute the SQL Script that creates the described database.
Start by creating tables without any foreign keys. If you try to create tables with foreign keys that reference other tables that are not yet created, you will get an error.
In our database schema, the Department, Organization, and Office tables have no foreign keys. The following script creates a dummy database named Organization and adds three tables in it: Department, Insurance, and Office.
CREATE DATABASE Organization
USE Organization
CREATE TABLE Department
(
Id INT PRIMARY KEY,
Name VARCHAR (50) NOT NULL
)
USE Organization
CREATE TABLE Insurance
(
Id INT PRIMARY KEY,
Name VARCHAR (50) NOT NULL
)
USE Organization
CREATE TABLE Office
(
Id INT PRIMARY KEY,
Name VARCHAR (50) NOT NULL
)
You can use the FOREIGN KEY REFERENCES constraint to implement a foreign key relationship in SQL Server. Specify the table name. Then specify in parenthesis the column name for the foreign key to reference it.
The following script creates the Employee table with foreign keys Dep_Id and Insur_Id that reference Id columns in the Department and Insurance tables respectively.
USE Organization
CREATE TABLE Employee
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR (50) NOT NULL,
Age INT,
Gender VARCHAR (50),
Dep_Id int FOREIGN KEY REFERENCES Department(Id),
Insur_Id int FOREIGN KEY REFERENCES Insurance(Id)
)
Finally, the following script creates the Employee_Office table with two foreign keys Emp_Id and Office_Id.
USE Organization
CREATE TABLE Employee_Office
(
Id INT PRIMARY KEY IDENTITY(1,1),
Emp_Id int FOREIGN KEY REFERENCES Employee(Id),
Office_Id int FOREIGN KEY REFERENCES Office(Id)
)
Inserting Records in Tables with Multiple Foreign Keys
To insert records into tables with multiple foreign keys, you should first create corresponding records in the tables that are referenced by foreign keys in the original tables.
In practice, to insert records into the Employee table, we must first create corresponding records in the Department and Insurance tables. It is because the Employee table contains foreign keys referencing the Department and Insurance tables.
First, we try to add records into the Employee table without specifying foreign keys referencing the Department and Employee tables.
INSERT INTO Employee
VALUES ('James', 10, 'Male'),
('Sara', 7, 'Female')
You will see the following error. It occurs because the number of values specified doesn’t match the number of columns in the Employee table.
Let’s try to add some dummy values for the Dep_Id and Insur_Id columns (foreign keys):
INSERT INTO Employee
VALUES ('James', 10, 'Male', 2, 2),
('Sara', 7, 'Female', 1, 1)
You will see the following error since the Department and Insurance tables don’t have records with ids 2 and 1, respectively.
Let’s now insert records into the Department, Insurance, and Office tables:
INSERT INTO Department
VALUES (1, 'Finance'),
(2, 'HR')
INSERT INTO Insurance
VALUES (1, 'Company A'),
(2, 'Company B')
INSERT INTO Office
VALUES (1, 'Paris'),
(2, 'London')
Since the Department and Insurance tables now have records with Ids 2 and 1, you can insert records into the Employee tables with corresponding foreign key values as shown below:
INSERT INTO Employee
VALUES ('James', 10, 'Male', 2, 2),
('Sara', 7, 'Female', 1, 1)
Insert some records into the Employee_Office table. But before that, let’s see the Id column values in the Employee table:
SELECT * FROM Employee
The Employee column contains records with Id values of 2 and 3. You can insert records into the Employee_Office table where the Emp_Id column contains 2 or 3, and the Office_Id column contains 1 or 2.
INSERT INTO Employee_Office
VALUES (2, 1),
(2, 2),
(3,2)
Selecting Records from Tables with Multiple Foreign Keys
To select records from tables with Multiple foreign keys, you need JOINs.
The following script returns values of the Name and Gender columns from the Employee table and the Name columns from the Department and Insurance tables. Since the Employee table contains two foreign keys, you have to use two LEFT JOIN statements:
SELECT
Employee.Name AS Employee_Name,
Employee.Gender,
Department.Name as Department_Name,
Insurance.Name as Insurance
FROM Employee
LEFT JOIN Department ON Employee.Dep_Id = Department.Id
LEFT JOIN Insurance ON Employee.Insur_Id = Insurance.Id
In the same way, you can select values of the Name and Gender columns from the Employee table and the Name column from the Office table using two LEFT JOIN statements on the lookup table Employee_Office.
SELECT
Employee.Name AS Employee_Name,
Employee.Gender,
Office.Name as Office_Name
FROM Employee
LEFT JOIN Employee_Office ON Employee.Id = Employee_Office.Emp_Id
LEFT JOIN Office ON Office.Id = Employee_Office.Office_Id
DELETING Records from Tables with Multiple Foreign Keys
You can delete records from tables with multiple foreign keys. However, ensure that the table is not referenced by a foreign key in another column.
For instance, you should not delete records from the Department table that is referenced by the Emp_Id foreign key in the Employee table. Here is an example:
DELETE FROM Department WHERE Id = 1
Since the record with Id 1 in the Department table is referenced by the Emp_Id column in the Employee table, you cannot delete it as mentioned in the above error. First, you have to delete all records from the Employee table where Emp_Id is 1.
The Employee table contains only 1 such record (with the Id value of 3). Let’s try to delete that record with the following query:
DELETE FROM Employee WHERE Id = 3You will see the error which occurs because Employee_Office contains a record where the Emp_Id foreign key column contains 3 records. Hence you will need to remove that record first:
The following script removes the record with the Emp_Id value of 3 from the Employee_Office table.
DELETE FROM Employee_Office WHERE Emp_Id = 3Finally, the following script removes the record with Id values of 1 from the Employee and Department columns.
DELETE FROM Employee WHERE Id = 1
DELETE FROM Department WHERE Id = 1
Conclusion
Thus, we have examined adding multiple foreign keys in SQL Server tables using the SQL queries to add foreign keys. Hope that these practical examples used in the article also helped you to master this topic.
When you deal with foreign keys and table dependencies to perform the work tasks, it is also helpful to use the software tools to simplify the routines. Highly recommended is the Visual Database Diagram as a dedicated solution for viewing all dependencies between tables. Also, you can generate precise documentation for the entire database, no matter its complexity with the Database Documenter feature.
Both these tools aim to reduce manual load, automate the processes, and speed up the performance to take some burden off your shoulders.
Tags: foreign key, sql server tables Last modified: August 08, 2022
