Create a SQL Server table with foreign key constraints (2023)

With:Jan Potgieter| Updated: 08/09/2022 |Comments| Related:1|2|3|4|5|6|7|8|9|10|11|Only>TSQL


Problem

A database is a store of information and often the data is linked together. When creating a database and tables to store information, it makes sense to link different parts together. In this article, we look at how to create SQL Server tables and use foreign keys to refer to the related data in the different tables.

Solution

This SQL tutorial shows you how to create a SQL Server database that contains related information. To illustrate, let's create an HR database with two tables that store information about companies and employees.

We will work with the following:

  • Create two tables with a linkwill notProfession.
  • Create two tables with a linkI shallProfession.
  • Add a foreign key to enforce referential integrity between the two tables.
  • Delete data from tables.
  • Update and remove data to show how the foreign key protects data.
  • Use the foreign key cascading option.

Set the script

First, the following factors are relevant to this scenario:

  • Company Table: List of companies
  • Employee Table: List of employees working in the company
  • Rule:Employees can only work for one company and a company can employ many people.

Let's create the environment in which we have to work. Open SQL Server Management Studio (SSMS), open a query window and run the following SQL commands to create the database you need. Note: When creating a production database, remember proper planning, e.g. Create the data file and the log file on separate disks and not on the C:\ drive.

For this SQL tutorial, we will create a SQL database using SQL Server's default settings.

--Create HRDatabase USE master GO DROP DATABASE IF FOUND HRDatabase GO CREATE DATABASE HRDbase GO USE HRDatabase GO

Create two new tables with a broken link

Let's create onecompaniestable, oneEmployeestable and enter some sample data using the following syntax:

--1. Experiment: Create a company and an employee table and link them together. SEND TABLE IF YOU FIND firm; GO TO THE TABLE IF THERE ARE STAFF; GO – SQL statement CREATE TABLE CREATE TABLE Companies ( ID INT CONSTRAINT PK_Companies PRIMARY KEY IDENTITY, CompanyName VARCHAR(80) NOT NULL, – column name, data types and null value CompAddress VARCHAR(80) NOT NULL(No20ARCHCo) NOT NULL, empid int not null , create datetime not null, constraint df_companies_createdate default getDate()) create table Employees(id int -constraint pk_employmenter primary key id, EmploymentName varchar(80) not null, contactno varchar(20) not null, email varchar(80) NOT NULL, CreateDate DATETIME NOT NULL constraint DF_Employees_CreateDate DEFAULT getdate() ) INSERT INTO Company (CompanyName, CompAddress, CompContactNo, EmpID) VALUES ('Alpha Company' 298', Garden', 29, 29, 29, 21, 20, 20 , 20 , 200, 200, 200, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 200, 200, 80, 80 , 1) , ('Bravo Company', ' 456 South Street, Brooklyn, Pretoria, '091.523.4789', 1), ('Charlie Company', '987 West Street, Lynnwood, Pretoria', '091.523.1235', 1), ('Delta Company', '258 East Street, The Meadows, Pretoria, '091 523 7414', 1), ('Echo Company', '100 Amber Street, Hatfield, Pretoria', '091 523 9685', 1) INSERT INTO Employees (Employee Name, Contact Number, email) VALUES ('Joe Blogs' , '012 365 4789', '[email protected]') , ('Jane Doe' , '012 365 4789', '[email protected]') , ('John Smit' , '012 365 4789', '[email protected]') , ('Eddy Jones', '012 365 4789', '[email protected]'), ('Steve Dobson', '012 365 4789', '[email protected]') SELECT * FROM Companies SELECT * FROM Employees

LikeEmpIDThe Companies column has been added to the Companies table and linked to the Employees tableIDSplit.

Looking at the result below, something is wrong! That means one employee works for five different companies! According to the above database scenario, this is impossible.

Create a SQL Server table with foreign key constraints (1)

Create a SQL Server table with foreign key constraints (2)

Rule:Employees can only work for one company and a company can employ many people.

There is a one-to-many relationship between the two tables. One employee can work for multiple companies, which is not the norm in this scenario. So that's not the right way to set it up.

Create two new tables with a working link

Let's recreate the company and employee tables but change the association between them with the following syntax:

-- Second attempt: Create a company and an employee table and link them together. SEND TABLE IF COMPANIES FOUND? GO TO THE TABLE IF THERE ARE STAFF; GO – SQL statement CREATE TABLE CREATE TABLE Companies ( ID INT CONSTRAINT PK_Companies PRIMARY KEY IDENTITY, – primary key constraint company name VARCHAR(80) NOT NULL, – define column CompAddress VARCHAR(80) NOTCHnta NULL(No20)) null, create datetime not null constraint df_companies_createdate default getDate()) create table Employees( id int -constraint pk_employmenter primary key id, employment name varchar(80) not null, contactno varchar(20) not null, email varchar(80) not null, compid INT NOT NULL, CreateDate DATETIME NOT NULL constraint DF_Employees_CreateDate DEFAULT getdate() ) INSERT INTO Company (CompanyName, CompAddress, CompContactNo) VALUES ('Alpha Company', '1293 North Street', '129,'Garsatore2,', '129, North Street ') ; Street', '129,20,', '129, North,', '129,' '091 52' 'Bravo Company', '456 South Street, Brooklyn, Pretoria' '091 523 4789' ), ( "Charlie Company ", "987 West Street, Lynnwood, Pretoria", "091 523 1235", ("Delta Company", "258 East Street, The Meadows, Pretoria", "091 523 7414"), ("Echo Company". ', ' 100 Amber Street, Hatfield, Pretoria', '091 523 9685') INSERT IN Employee(Employee Name, Contact Number, Email, CompID) VALUES ('Joe Blogs', '012 365 4789', '[email protected]', 1), ('Jane Doe', '012 365 4789', '[email protected]', 2), ('John Smit' , '012 365 4789', '[email protected]', 1), ('Eddy Jones' , '012 365 4789', '[email protected]' , 4), ('Steve Dobson' '012 365 4789', '[email protected]', 5) SELECT * FROM Companies SELECT * FROM Employees

This time we've added a column to the Employees table, CompID, which is linked to the ID column in the Company table.

The result set now makes more sense because a single company can have more than one employee. This is consistent with the script we created for this demonstration.

Look at the result set and SQL diagram for the two tables below.

Create a SQL Server table with foreign key constraints (3)

Create a SQL Server table with foreign key constraints (4)

This will work for our script! Several employees can work for the same company.

This in turn means that there is a one-to-many relationship between the two tables, but this time a collaborator can work on itonlyCompany that conforms to the rules of the scenario.

Add a foreign key to enforce referential integrity between two tables

Although we correctly created the existing tables, there is still no join (or foreign key) connecting the two tables. Now let's enforce data integrity by adding a foreign key.

A FOREIGN KEY is a field in one table that references the PRIMARY KEY of another table.

Check out some good websites that explain more about foreign keys:

  • How to create a foreign key for SQL Server
  • SQL FOREIGN KEY constraint

Using the tables created in the section above, we will make the following change to add a foreign key constraint and add a new collaborator.

--Now let's enforce data integrity by adding foreign keys ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Companies FOREIGN KEY (CompID) REFERENCES dbo.Companies (ID) --Let's add some employees associated with a company from list 9 INSERT INTO employees ( EmployeeName, ContactNumber, Email, CompID) VALUES ("Joe Blogs", "012 365 4789", "[email protected]', 6)

We're getting this error because the company doesn't exist. The restriction strengthened data integrity by preventing the addition of an employee who works for a company that doesn't exist!

--Error: --Msg 547, Level 16, State 0, Line 151 - The INSERT statement conflicted with the FOREIGN KEY constraint 'FK_Employees_Companies'. --Conflict occurred in database "HRDatabase", table "dbo.Companies", column "ID". --Declaration complete.

We'll add the company and try staff deployment again.

--Let's add the company. INSERT INTO Companies (CompanyName, CompAddress, CompContactNo) VALUES ('Foxtrot Company', '123 North Street, Garsfontein, Pretoria', '091 523 6987') SELECT * FROM Companies -- Now try again with Employee Who works for ID=6, e.g. B. Foxtrot Company INSERT INTO Employees (EmployeeName, ContactNo, Email, CompID) VALUES ('Joe Blogs' , '012 365 4789', '[email protected]', 6) SELECT * FROM Companies SELECT * FROM Employees

It worked this time.

In the image below we can see how the two tables are connected. The key on the relationship line means one and the infinity symbol on the other side means many. Hence the one-to-many relationship between tables.

Create a SQL Server table with foreign key constraints (5)

Delete data from tables

Let's try deleting the new company we just created that has an employee associated with it and see what happens.

--Now what happens when you remove a company that an employee works for? DELETE FROM company with ID = 6

We're getting this error and can't remove a company with one employee.

--Error: --Msg 547, Level 16, State 0, Line 184 - The DELETE statement conflicts with the REFERENCE constraint 'FK_Employees_Companies'. --Conflict occurred in database "HRDatabase", table "dbo.Employees", column "CompID". --Declaration complete.

If we first remove an employee who works for company 6, we can also remove the company.

DELETE Employees with ID = 7 - Now try to remove the company. DELETE Company with ID = 6. SELECT * FROM Companies. SELECT * FROM Employees

As you can see, when setting a foreign key between two tables, the deletion must be done in the right order.

Refresh the data to show how the foreign key protects the data

When updates are made, such as B. changing the CompID in the employee table, it works as shown below.

--What will happen when Joe Blogs leaves Alpha Company and joins Echo Company? UPDATE Employees SET CompID = 5 WHERE ID = 1 SELECT * FROM Companies SELECT * FROM Employees – No problem! --What if Eddie Jones quits and joins the Charlie Company? UPDATE Employees SET CompID = 3 WHERE ID = 4 SELECT * FROM Companies SELECT * FROM Employees

Again, if the foreign keys are set up correctly and the updates to the records are done correctly and within the rules, everything works fine.

Selection of the foreign key cascade

To wrap up this tutorial, let's look at foreign key scaling. Cascade selection takes care of the entries in the child table when an UPDATE or DELETE is performed on the parent table.

--Let's drop the foreign key and cascade it to drop ALTER TABLE dbo.Employees DROP CONSTRAINT FK_Employees_Companies GO ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Companies FOREIGN KEYRETIONRECompanies (ACIONREIDRE) CASCADE GO company) closes? --Let's remove the company with ID = 1 (alpha company) from the companies table. SELECT * FROM Company. SELECT * FROM "Employees". DELETE FROM "Company with ID = 1". SELECT * FROM Company. SELECT * FROM "Employees".

In the code above, when the company is deleted, all employee rows associated with the company are also deleted using the foreign key based on the specified DELETE CLASS option.

Again, if the rules are configured correctly, the database protects data integrity.

Cleanup by deleting the HRDatabase database

You can run the following code to backup the sample database we created.

USE master? GO --DROP DATABASE IF EXISTS DROP DATABASE IF EXISTS HRD database. I AM GOING
Next Step

The next step is to consider how to create all the supporting tables needed in a database to support a simple web application.

Further literature can be found under the following links:

  • How to create a foreign key for SQL Server
  • List of SQL Server foreign key dependencies
  • Tips on SQL Server Constraints
About the author

Jan Potgieter has more than two decades of experience in the database industry as a certified Microsoft SQL Server administrator and database developer.

Check out all my tips

Article last updated: 09.08.2022

References

Top Articles
Latest Posts
Article information

Author: Carlyn Walter

Last Updated: 04/10/2023

Views: 5285

Rating: 5 / 5 (70 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Carlyn Walter

Birthday: 1996-01-03

Address: Suite 452 40815 Denyse Extensions, Sengermouth, OR 42374

Phone: +8501809515404

Job: Manufacturing Technician

Hobby: Table tennis, Archery, Vacation, Metal detecting, Yo-yoing, Crocheting, Creative writing

Introduction: My name is Carlyn Walter, I am a lively, glamorous, healthy, clean, powerful, calm, combative person who loves writing and wants to share my knowledge and understanding with you.