Foreign Key in SQL with Examples

Introduction

The concept of foreign keys in Structured Query Language (SQL) is fundamental to database administration and helps to preserve data integrity.

The Foreign key has always played an important role in ensuring the coherence and reliability of stored data. This has happened even in the early days of relational databases.

In this guide, we will discuss what a Foreign key in SQL is all about. We will look at its syntax and practical applications through examples.

Overview

Foreign Keys are vital parts of relational database administration. They make it easier for tables to connect meaningfully with one another. Foreign Keys guarantee the accuracy and consistency of data relationships by enforcing referential integrity. This prevents the entry of invalid or orphaned data.

You will have a thorough grasp of Foreign Keys in SQL and how they may improve the dependability and efficiency of your database systems by the end of this guide.

What is a Foreign Key in SQL?

A Foreign Key in SQL is an important component for maintaining the links between tables in a relational database. ​

When you establish a foreign key constraint, you are essentially specifying a column (or columns) in one table that refers to the primary key in another table. This establishes a parent-child connection between the two tables, with the parent table being the table having the primary key and the child table being the table containing the foreign key.

A Foreign Key and a Primary Key in SQL are different. In SQL, a Primary Key uniquely identifies each record in a table, while a Foreign Key establishes a relationship between two tables by referencing the Primary Key of another table. So Primary and Foreign Keys in SQL are different

Syntax of the Foreign Key SQL query:

CREATE TABLE table_name (

column1 data_type,

column2 data_type,..,

FOREIGN KEY (column_name)

REFERENCES referenced_table_name (referenced_column_name)

);

• CREATE TABLE table_name: This is the command to create a new table in the database.
• (column1 data_type, column2 data_type, ...): These are the columns of the new table, along with their respective data types.
• FOREIGN KEY (column_name): This line specifies that a Foreign Key constraint is being added to the specified column.
• REFERENCES referenced_table_name (referenced_column_name): This line indicates which table and column the Foreign Key references. It establishes a relationship between the column in the current table and the referenced column in another table.

Let's now examine an example and provide an explanation:

Assume that ‘Employees’ and ‘Departments’ are our two tables.

Departments Table:

Employees Table:

Each employee belongs to a department, and we want to ensure that the department referenced by an employee exists in the ‘Departments’ table.

We would use these commands: CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DepartmentID INT,

FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)

);

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,

DepartmentName VARCHAR(50)

);

• In the ‘Employees’ table, the column DepartmentID is specified as a Foreign Key using the FOREIGN KEY (DepartmentID) statement.
• The REFERENCES Departments(DepartmentID) statement indicates that the DepartmentID column in the ‘Employees’ table references the DepartmentID column in the ‘Departments’ table.
• This ensures that every value in the DepartmentID column of the ‘Employees’ table corresponds to a valid DepartmentID value in the ‘Departments’ table, maintaining data integrity.
  

Two Methods to Add a Foreign Key in SQL:

Here are two ways to add a Foreign Key in SQL:

1. Adding Foreign Key during Table Creation

When you are creating a new table and want to establish a foreign key relationship right away, you can incorporate the FOREIGN KEY constraint within the CREATE TABLE statement. Here is the Foreign Key SQL syntax during table creation:

CREATE TABLE table_name (

column1 data_type,

column2 data_type,

...

FOREIGN KEY (column_name) REFERENCES referenced_table_name(referenced_column_name)

);

Let's consider an example: To create a foreign key in orders table during creation:

CREATE TABLE orders (

order_id INT PRIMARY KEY,

customer_id INT,

order_date DATE,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

In this example, the customer_id column in the orders table is designated as a Foreign Key, referencing the customer_id column in the ‘customers’ table. This ensures that any value entered in the customer_id column of the ‘orders’ table must already exist in the ‘customers’ table, maintaining referential integrity.

Table Created During Table Creation:

2. Adding Foreign Key After Table Creation

If you have an existing table and need to establish a Foreign Key relationship afterward, you can utilize the ALTER TABLE statement along with the ADD CONSTRAINT clause. Here is the syntax to add a Foreign Key in SQL after table creation.

ALTER TABLE table_name

ADD CONSTRAINT constr_name FOREIGN KEY (col_nam)

REFERENCES referd_tablecreated(referenced_columnnamecreated);

• ALTER TABLE: This command allows modification of an existing table structure.
• table_name: Name of the table to which the foreign key constraint will be added.
• ADD CONSTRAINT constr_name: Declaration of a new constraint with a unique name.
• FOREIGN KEY (column_name): Declaration of a column as a foreign key.
• REFERENCES referd_tablecreated(referenced_columnnamecreated): Specifies the referenced table and column for the foreign key constraint.

Let's illustrate with an example. To add a Foreign Key after the creation of an ‘orders’ table, use this command.

ALTER TABLE orders

ADD CONSTRAINT fk_custid FOREIGN KEY (customer_id) REFERENCES customers(cust_id);

Here, the FOREIGN KEY constraint named 'fk_custid' is added to the ‘orders’ table. The cust_id column in the ‘orders’ table is linked to the cust_id column in the ‘customers’ table, ensuring that each value in the cust_id column of the ‘orders’ table corresponds to a valid entry in the ‘customers’ table, thus preserving referential integrity.

Table Created After Table Creation:

Inserting Records in Table With Foreign Key:

When inserting records into a table with Foreign Keys, you must ensure that the values you are inserting into the Foreign Key columns exist in the referenced table.

For instance, let's say we have two tables: ‘orders’ and ‘customers’. A Foreign Key field called customer_id in the ‘orders’ table refers to the customer_id column in the ‘customers’ table. Here is how you can insert records into the ‘orders’ table:

INSERT INTO orders (order_id, customer_id, order_date) VALUES

(1, 101, '2023-03-15'),

(2, 102, '2023-03-16'),

(3, 103, '2023-03-17');

Ensure that the customer_id values (101, 102, 103) exist in the ‘customers’ table before executing the above insert statement. We would now have an output table like this:

How to DROP a Foreign Key in SQL

To drop a Foreign Key constraint, you can use the ALTER TABLE statement with the DROP CONSTRAINT clause. For example, to drop a foreign key named fk_customer_id from the ‘orders’ table, you would use the following SQL:

ALTER TABLE orders DROP CONSTRAINT fk_customer_id;

This command removes the foreign key constraint fk_customer_id from the ‘orders’ table.

Adding Multiple Foreign Keys in a Table:

A table can have multiple Foreign Keys to establish relationships with different tables. For instance, consider a scenario where we need to record transactions where each user acts as both a buyer and a seller. Here's an example:

CREATE TABLE Transactions (

transaction_id INT PRIMARY KEY,

amount INT,

seller INT,

buyer INT,

CONSTRAINT fk_sel FOREIGN KEY (theseller) REFERENCES Users(id),

CONSTRAINT fk_buy FOREIGN KEY (thebuyer) REFERENCES Users(id)

);

In this example, the ‘Transactions’ table contains two Foreign Keys (seller and buyer), both referencing the id column in the ‘Users table. This allows us to maintain relationships

between transactions and users acting as buyers or sellers.

Output Table:

This represents the ‘Transactions’ table where transactions are recorded, with two foreign keys (seller and buyer) referencing the id column in the ‘Users’ table.

Why Should You Use a Foreign Key in SQL?

Foreign keys play a crucial role in maintaining data integrity and establishing relationships between tables in a relational database. Here are several reasons why you should utilize Foreign Keys:

1. To Normalize Data

Foreign Keys aid in normalizing data across multiple tables, reducing redundancy and improving database efficiency. By breaking down large datasets into smaller and related tables, you can organize data logically and prevent duplication.

For example, in a database for an e-commerce platform, you might have separate tables for customers, orders, and products. The use of Foreign Keys allows you to link these tables together based on relationships, such as associating each order with a specific customer.

2. Prevent Wrong Data from Insertion

Foreign Keys help enforce referential integrity, ensuring that only valid data can be inserted into related tables. When a Foreign Key constraint is applied, it restricts the values that can be entered into a column to those that exist in the referenced table's primary key column. By doing this, errors and inconsistencies in the database are avoided.

3. It Helps Maintain Consistency Across Tables

Foreign Keys facilitate maintaining consistency across related tables by enforcing data relationships. When you update or delete records in one table, the Foreign Key constraints automatically ensure that corresponding changes are made in related tables, preserving data integrity.

For example, in a database for a library, if a book record is deleted, Foreign Key constraints ensure that associated records, such as borrowing history or book availability, are also appropriately updated or removed.

4. It Improves Query Performance

By defining Foreign Key relationships between tables, SQL databases can optimize query performance through efficient indexing and query execution plans. This allows for faster data retrieval and manipulation operations, leading to improved overall system performance.

For instance, in a database for a social media platform, Foreign Keys can expedite searching for posts by a specific user by leveraging indexed foreign key columns.

5. Promote Data Consistency and Accuracy

Foreign Keys promote data consistency and accuracy by preventing or restricting the insertion of invalid or inconsistent data into related tables. This ensures that the database remains reliable and trustworthy, supporting robust decision-making processes.

In a database tracking employee information and their assigned departments, Foreign Keys ensures that only existing department IDs can be assigned to employees, eliminating errors in department assignments.

6. Ensure Data Integrity

Foreign Keys enforce data integrity by preventing orphaned records and maintaining the referential integrity of the database. Orphaned records, which are records in a child table that no longer have a corresponding record in the parent table, are prevented by Foreign Key constraints. This ensures that all data in the database remains valid and consistent, minimizing the risk of data corruption and ensuring the reliability of the database.

SQL Foreign Key Syntax in Different Databases

When it comes to implementing Foreign Keys in SQL databases, the syntax may vary slightly depending on the Database Management System (DBMS) you are using. Let's discuss the Foreign Key syntax for MySQL, SQL Server, Oracle, and MS Access in a tabular format:

Winding Up!

In summary, Foreign Keys in SQL are really important for keeping your data organized and accurate. They help make sure that the relationships between different parts of your database are strong and reliable.

By using Foreign Keys, you can prevent mistakes and make your database work better. Using Foreign Keys is a smart move that can make a big difference in how well your database works. This is true whether you're setting up a new database or changing an existing one,

With the knowledge you have gained from this guide, you will be able to use Foreign Keys effectively and improve the quality of your database.

FAQs

1. What is a Foreign Key in SQL?
   
   A Foreign Key in SQL is a field or a combination of fields in a table that uniquely identifies a record in another table.
   
2. What distinguishes a Foreign Key from a Primary Key?
   
   The Primary Key uniquely identifies each record in a table, while a Foreign Key establishes a link between two tables, referencing the Primary Key of another table.
   
3. What distinguishes a Reference Key from a Foreign Key?
   
   A Foreign Key is a field or a combination of fields in one table that matches the Primary Key of another table, while a Reference Key refers to any field or combination of fields that are being referenced from another table.
   
   
4. How do I insert a foreign key?
   
   To insert a Foreign Key, you first define it in the table schema by specifying the column(s) that will act as the foreign key and then use the appropriate SQL syntax to establish the relationship between tables. This is the syntax: ALTER TABLE child_table

ADD CONSTRAINT fk_name

FOREIGN KEY (foreign_key_column)

REFERENCES parent_table(primary_key_column);

5. What would be an example of Foreign Key?
   
   An example of a Foreign Key is a customer_id column in an ‘orders’ table, which references the id column in a ‘customers’ table. This ensures that each order is associated with a specific customer.
   
6. What is the use of Foreign Keys?
   
   Foreign Keys ensure referential integrity in a database by enforcing relationships between tables. They help maintain consistency and prevent orphaned records.
   
7. Why do we need a Foreign Key?
   
   Foreign Keys are needed to establish relationships between tables in a relational database, ensuring data integrity and consistency across the database.
   
   
8. Can I have 2 Foreign Keys reference the same Primary Key?
   
   Yes, you can have multiple Foreign Keys referencing the same Primary Key in a table, allowing for different types of relationships between tables.
   
9. Is a Foreign Key unique?
   
   Foreign Keys do not have to be unique themselves, but they must reference unique values in the Primary Key of another table to maintain referential integrity.
   
   
10. How do you identify a Foreign Key?
    
    You can identify a Foreign Key by looking at the table schema or by examining the relationships between tables in the database schema. In some database management systems, there are specific commands or queries you can use to view the Foreign Keys defined in a table.