How to Create Foreign Key in SQL: Best Practices and Examples

SQL foreign key is a field in a table that points to the primary key of another table. It links two tables together and creates a relationship between them based on the common fields. 

This relationship is established when a column in one table has the same values as the primary key column of another table.

In SQL, when you establish a relationship between two tables using a foreign key, one table is referred to as the child table, while the other is called the parent or reference table. The child table contains the foreign key, which refers to the parent table’s primary key.

A foreign key in SQL is a constraint that ensures that the values in a column of a table are valid references to the primary key values in another table. It means a foreign key can only reference a primary key in the same or a different table.

Foreign keys are an important for creating well-structured and maintainable databases in SQL. They help maintain data consistency and prevent data inconsistencies. They are also used to enforce referential integrity, meaning that a record cannot be deleted if referenced by a record in another table.

To understand how foreign keys work, let’s also understand primary keys in SQL.

What is a Primary Key?

A primary key in SQL is a column or set of columns that uniquely identify each row in a table. It must contain unique and non-null values. The primary key constraint ensures that there are no duplicate rows in the table and provides a fast way to search for specific records. 

Primary keys can be used as a reference by foreign keys in other tables to establish relationships between tables. In short, a primary key is a unique identifier for each record in a table.

Why do we use Foreign Key in SQL?

There are several reasons why we use foreign keys in SQL:

1. Data integrity: One of the main reasons to use foreign keys is to ensure data integrity. By creating a relationship between two tables using a foreign key constraint, you can prevent data from being inserted into the child table that does not exist in the parent table. It helps to maintain the consistency and accuracy of the data in your database.
2. Referential integrity: Foreign keys also help to maintain referential integrity between related tables. It means that any changes made to the data in the parent table will automatically propagate to the child table, ensuring that the data is always consistent.
3. Improved performance: By using foreign keys to establish relationships between tables, you can improve the performance of your queries. This is because the database engine can use the foreign key relationship to optimize the execution of queries that join the related tables.
4. Ease of maintenance: Foreign keys make it easier to maintain your database over time. For example, if you need to modify the structure of the parent table, the changes will automatically propagate to the child table without requiring manual updates.

Example of Foreign Key in SQL Server

To better understand how foreign keys work, let us take an example of two tables, Customers and Orders. The Customers table contains the customer information, and the Orders table contains the order information.

In this example, the Orders table has a foreign key constraint referencing the Customers table’s primary key. It ensures that the CustomerID values in the Orders table are valid references to the ID values in the Customers table.

Creating Foreign Key

-- The Customers table contains Primary key
CREATE TABLE Customers (
    ID int PRIMARY KEY,
    CustomerName varchar(255) NOT NULL,
    ContactName varchar(255),
    Country varchar(255)
);
-- The Orders table contain foreign key referencing ID column of Customers table.

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate date,
    TotalAmount decimal(10,2),
    CONSTRAINT FK_CustomersOrders FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);

Adding Data to a Table with a Foreign Key Constraint

We can then insert some dummy data into the Customers and Orders table using the following SQL code:

INSERT INTO Customers (ID, CustomerName, Country)
VALUES
(1, 'Shekh Ali', 'India'),
(2, 'Alice Johnson', 'Canada'),
(3, 'Roman Chug', 'Australia'),
(4, 'Mohammed Ali', 'Egypt');

-- Inserting data into the Orders table using the following SQL query:
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
    (1, 1, '2023-03-15', 100.50),
    (2, 2, '2023-03-16', 75.00),
    (3, 3, '2023-03-17', 200.00),
    (4, 1, '2023-03-18', 50.25),
    (5, 4, '2023-03-19', 125.75);

Customers Table:

Orders Table:

As you can see, the Customers table has four rows of data, with a ID column that serves as the primary key.

The Orders table has five rows of data, with a CustomerID column that serves as a foreign key to the ID column in the Customers table, enforcing referential integrity between the two tables.

SQL Foreign Key Constraint On CREATE TABLE:

You can add a foreign key constraint to an existing table using the ALTER TABLE statement. Here is the syntax for adding a foreign key constraint.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column)
);

For example, to create a foreign key constraint on the Orders table to reference the Customers table, you can use the following SQL statement:

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate date,
    TotalAmount decimal(10,2),
    CONSTRAINT FK_CustomersOrders FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);

SQL FOREIGN KEY constraint With ALTER TABLE:

We can use the ALTER TABLE statement to add a FOREIGN KEY constraint to an existing SQL server table. 

Here is an example of how you can use ALTER TABLE to add a foreign key constraint to the Orders table, referencing the ID column in the Customers table:

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomersOrders
FOREIGN KEY (CustomerID)
REFERENCES Customers(ID);

This SQL statement adds a foreign key constraint named FK_CustomersOrders to the Orders table, linking the CustomerID column to the ID column in the Customers table. The FOREIGN KEY keyword specifies this as a foreign key constraint, and the REFERENCES keyword indicates which table and column the constraint should reference.

After running this SQL statement, the Orders table will have a foreign key constraint that enforces referential integrity between the CustomerID column in the Orders table and the ID column in the Customers table.

Difference between primary key and foreign key in SQL:

In SQL, a primary key and a foreign key are both types of constraints used to establish relationships between tables. However, there are some key differences between them:

• Purpose: A primary key is a constraint used to uniquely identify each row in a table, while a foreign key is a constraint used to link two tables together based on a common column.
• Columns: A primary key is defined on a single column or a combination of columns uniquely identifying each row in the table. On the other hand, a foreign key is defined on a column or a set of columns that refer to the primary key of another table.
• Uniqueness: The values in a primary key column must be unique and cannot be NULL, while the values in a foreign key column do not have to be unique, but they must match the values in the primary key column of the related table.
• Usage: A primary key enforces data integrity by preventing duplicate records and ensuring that each row in the table can be uniquely identified. On the other hand, a foreign key is used to establish relationships between tables, allowing data to be retrieved and manipulated across multiple tables.
• Number: A table can have only one primary key constraint, while it can have multiple foreign key constraints.

The main difference between a primary key and a foreign key is that a primary key uniquely identifies each row in a table, while a foreign key links two tables together based on a common column.

CASCADE and SET NULL Actions

When a foreign key is defined, you can specify what happens to related rows in the child table when a row in the parent table is updated or deleted. There are two common actions that can be taken:

1. CASCADE: When a row in the parent table is updated or deleted, the corresponding rows in the child table are also updated or deleted automatically.
2. SET NULL: When a row in the parent table is updated or deleted, the corresponding foreign key values in the child table are set to NULL.

Here is an example of how to use these actions:

-- Add a foreign key constraint with CASCADE action
ALTER TABLE Orders
ADD CONSTRAINT FK_CustomersOrders
FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
ON UPDATE CASCADE ON DELETE CASCADE;

-- Add a foreign key constraint with SET NULL action
ALTER TABLE Orders
ADD CONSTRAINT FK_CustomersOrders
FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
ON UPDATE SET NULL ON DELETE SET NULL;

In the first example, we’ve added a foreign key constraint with the CASCADE action, which means that when a row in the Customers table is updated or deleted, the corresponding rows in the Orders table will also be updated or deleted.

In the second example, we’ve added a foreign key constraint with the SET NULL action, which means that when a row in the Customers table is updated or deleted by someone, then the corresponding foreign key values in the Orders table will be set to NULL automatically.

Self-Referencing Foreign Keys

Sometimes, you may create a foreign key constraint referencing the same table defined. It is known as a self-referencing foreign key. It can be useful for modeling hierarchical relationships or other types of recursive data structures.

Here is an example of how to create a self-referencing foreign key:

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    ManagerID int,
    EmployeeName varchar(255) NOT NULL,
    CONSTRAINT FK_EmployeesManager
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

Indexing Foreign Keys

In some cases, indexing the columns used in foreign key constraints may be useful. This can improve query performance and reduce the time it takes to retrieve data from related tables.

Here is an example of how to create an index on a foreign key column:

CREATE INDEX IX_Customers_CustomerID
ON Customers (CustomerID);

In this example, we have created an index on the CustomerID column in the Customers table, which is used as a foreign key in the Orders table. This helps speed up queries that join these two tables together.

How to Drop a FOREIGN KEY Constraint?

You can drop a foreign key constraint in SQL using the ALTER TABLE statement. Here’s the syntax for dropping a foreign key constraint:

Syntax:

ALTER TABLE child_table
DROP CONSTRAINT fk_constraint_name;

In this syntax, child_table refers to the child table that contains the foreign key constraint you want to drop, and fk_constraint_name is the name of the foreign key constraint you want to drop.

Here is an example of how to use the below SQL Query to drop a foreign key constraint:

ALTER TABLE Orders
DROP CONSTRAINT FK_CustomersOrders;

In this example, the foreign key constraint named FK_CustomersOrders is dropped from the Orders table.

It’s important to note that dropping a foreign key constraint can affect the integrity of the data in your tables. Therefore, you should only drop a foreign key constraint if you’re sure it’s no longer needed and have taken necessary precautions to ensure the data is consistent.

Summary:

In summary, foreign keys are an important part of a relational database. They are used to establish relationships between tables and ensure data consistency. A foreign key is a field in a table that points to the primary key of another table. In SQL, a foreign key is a constraint that ensures that the values in a column of a table are valid references to the primary key values in another table.

FAQs

References: MSDN-Primary key and Foreign Key

You might want to read this too:

• Mastering Database Normalization: Best Practices and Techniques
• Clustered Vs Non-Clustered Index in SQL
• Mastering SQL Inner Join
• Understanding CRM Databases
• Types of Joins in SQL Server
• Having VS Where Clause in SQL
• SQL EXISTS – Exploring EXISTS Operator in SQL Server
• Like operator in SQL
• Different ways to delete duplicate rows in SQL Server

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024