FOREIGN KEY in SQL

Overview

A foreign key is a column (or combination of columns) in a table that reference another table's primary key. It is used to establish and maintain relationships between tables. Foreign keys prevent actions that would leave the database in an inconsistent state. For example, they prevent the deletion of a record referenced by another table. They also enable the cascading of updates or deletes. When a record in the parent table is updated or deleted, the corresponding records in the child table are also updated or deleted.

Introduction

The marketing team at a furniture company was having difficulty tracking customer purchases. Their data was spread across multiple databases, and it was becoming increasingly difficult to manage. The IT team suggested using a Foreign Key in the SQL database to link customer information from different databases and make it easier to track customer purchases. Lets help them with further information about foreign key.

Let us take an example to explain it:

For example, consider a database that stores information about students and the classes they take. The Classes table contains a primary key, ClassID, that is used to identify each class. The Students table contains a foreign key, ClassID, that references the ClassID from the Classes table. This foreign key relationship ensures that when a record is deleted from the Classes table, any related records in the Students table are also deleted. Likewise, when a record is updated in the Classes table, any related records in the Students table are updated.

SQL FOREIGN KEY constraint ON CREATE TABLE:

Syntax

CREATE TABLE table_name
(
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT fk_name FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (parent_column1, parent_column2, ... parent_column_n)
);

This syntax is used to create a new table with a FOREIGN KEY constraint. The CONSTRAINT clause specifies the name of the FOREIGN KEY constraint and the columns it applies to. The REFERENCES clause specifies the parent table and the columns in the parent table that the FOREIGN KEY constraint will reference. The columns in the parent table must have the same data type as the columns in the child table.

Example

CREATE TABLE orders
(
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    CONSTRAINT fk_customer_id FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
);

Table: Orders

This example creates a new table called orders with a column for the order ID, a column for the customer ID, and a column for the order date. The CONSTRAINT clause specifies that the customer_id column should have a FOREIGN KEY constraint, referencing the customer_id column in the parent table called customers. This means that the value in the customer_id column in the orders table must exist in the customer_id column in the customers table.

SQL FOREIGN KEY constraint for ALTER TABLE:

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name) REFERENCES other_table(column_name);

Example

Table Name: Orders

Table Name: Customers

ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers(id);

This statement will add a foreign key constraint to the orders table, which will ensure that the customer_id column of the orders table only contains valid customer IDs that exist in the customers table. The foreign key will reference the id column of the customers table.

DROP SYNTAX for FOREIGN KEY COSTRAINT:

Syntax

ALTER TABLE <table_name> 
DROP CONSTRAINT <constraint_name>;

Example

Table Name: Customers

ALTER TABLE Customers
DROP CONSTRAINT FK_Customers_Countries;

This statement will remove the foreign key constraint named FK_Customers_Countries from the Customers table. The foreign key constraint was likely used to ensure that any entries in the Customers table had a corresponding entry in the Countries table. After executing the statement, the foreign key constraint will no longer exist, and the relationship between the two tables will no longer be enforced.

Difference between primary key and foreign key in SQL:

Primary Key

1. It is a column or a set of columns used to identify each row in a table uniquely.
2. It does not accept null values.
3. The Primary Key column does not allow duplicate values.
4. It can be used to define relationships between tables.
5. It cannot be changed.

Foreign Key

It is a column or set of columns used to create a link between two or more tables.

It can accept multiple null values.

The Foreign Key column can contain duplicate values.

It cannot be used to define relationships between tables.

It can be changed.

Conclusion

The Foreign Key allowed the marketing team to easily track and analyze customer purchases, giving them valuable insight into customer spending trends and helping them to better target their marketing campaigns. Foreign Key was helpful for them and allowed the marketing team to better understand their customers and create more effective marketing strategies.

Key takeaways

1. A foreign key is a column or group of columns in a relational database table that is used to establish and enforce a link between the data in two tables.
2. A foreign key constraint, also known as a referential integrity constraint, is used to prevent invalid data from being entered into the foreign key column of a child table.
3. A foreign key is used to reference a primary key in another table and cascade the changes or delete the related data if the primary key is updated or deleted.
4. In order to define a foreign key constraint, the data types of the columns involved must be compatible.
5. The foreign key should be indexed to improve the performance of queries using it.
6. It is important to ensure data integrity by using foreign key constraints.

Quiz

1. What is the purpose of a foreign key in SQL? 

1. To store related data 
2. To ensure data integrity 
3. To link two tables
4. To store data securely

Answer: c. To link two tables

2. What type of relationship is defined by a foreign key?

1. One-to-many 
2. Many-to-many
3.  One-to-one 
4. Parent-child

Answer: a. One-to-many

3. What is a cascading delete foreign key constraint in SQL? 

1. A foreign key that prevents data from being deleted 
2. A foreign key that allows data to be deleted 
3. A foreign key that prevents data from being updated 
4. A foreign key that allows data to be updated

Answer: b. A foreign key that allows data to be deleted

4. What happens when a foreign key is not indexed in SQL? 

1. The query will take longer to execute 
2.  The query will not execute 
3. The query will complete instantly 
4. The query will return an error

Answer: a. The query will take longer to execute