SQL Foreign Key is a constraint that is used to link two tables together. It is a field or a set of fields in one table that refers to the primary key of another table. The primary key of the referenced table is called the foreign key of the referring table. The foreign key constraint ensures that the data in the referring table is consistent with the data in the referenced table.
When a foreign key is defined, it creates a relationship between two tables. The relationship can be one-to-one, one-to-many, or many-to-many. The foreign key constraint ensures that the data in the referring table is consistent with the data in the referenced table. If a row is deleted from the referenced table, the foreign key constraint ensures that all the rows in the referring table that reference the deleted row are also deleted. This is called cascading delete.
Let's take an example to understand the concept of SQL Foreign Key. Suppose we have two tables, Customers and Orders. The Customers table has a primary key called CustomerID, and the Orders table has a foreign key called CustomerID that refers to the CustomerID field in the Customers table. This creates a relationship between the two tables, where each customer can have multiple orders.
The syntax for creating a foreign key constraint in SQL is as follows:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name) REFERENCES referenced_table_name(referenced_column_name);
Let's take an example to understand the syntax of SQL Foreign Key. Suppose we have two tables, Customers and Orders. The Customers table has a primary key called CustomerID, and the Orders table has a foreign key called CustomerID that refers to the CustomerID field in the Customers table. This creates a relationship between the two tables, where each customer can have multiple orders.
CREATE TABLE Customers (
CustomerID int PRIMARY KEY,
CustomerName varchar(255) NOT NULL,
ContactName varchar(255),
Country varchar(255)
);
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
OrderDate date NOT NULL,
CustomerID int,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
In the above example, we have created two tables, Customers and Orders. The Customers table has a primary key called CustomerID, and the Orders table has a foreign key called CustomerID that refers to the CustomerID field in the Customers table.
Now, let's insert some data into the Customers and Orders tables:
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Country)
VALUES (1, 'Alfreds Futterkiste', 'Maria Anders', 'Germany'),
(2, 'Ana Trujillo Emparedados', 'Ana Trujillo', 'Mexico'),
(3, 'Antonio Moreno Taquería', 'Antonio Moreno', 'Mexico'),
(4, 'Around the Horn', 'Thomas Hardy', 'UK'),
(5, 'Berglunds snabbköp', 'Christina Berglund', 'Sweden');
INSERT INTO Orders (OrderID, OrderDate, CustomerID)
VALUES (1, '2021-01-01', 1),
(2, '2021-01-02', 1),
(3, '2021-01-03', 2),
(4, '2021-01-04', 3),
(5, '2021-01-05', 3),
(6, '2021-01-06', 4),
(7, '2021-01-07', 5),
(8, '2021-01-08', 5);
In the above example, we have inserted some data into the Customers and Orders tables. We have inserted five customers and eight orders. The first two orders are for the first customer, the third order is for the second customer, the fourth and fifth orders are for the third customer, the sixth order is for the fourth customer, and the seventh and eighth orders are for the fifth customer.
Now, let's retrieve the data from the Customers and Orders tables:
SELECT * FROM Customers;
SELECT * FROM Orders;
The output of the above queries will be as follows:
Customers Table:
+------------+------------------------+----------------+---------+
| CustomerID | CustomerName | ContactName | Country |
+------------+------------------------+----------------+---------+
| 1 | Alfreds Futterkiste | Maria Anders | Germany |
| 2 | Ana Trujillo Emparedados | Ana Trujillo | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mexico |
| 4 | Around the Horn | Thomas Hardy | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Sweden |
+------------+------------------------+----------------+---------+
Orders Table:
+---------+------------+------------+
| OrderID | OrderDate | CustomerID |
+---------+------------+------------+
| 1 | 2021-01-01 | 1 |
| 2 | 2021-01-02 | 1 |
| 3 | 2021-01-03 | 2 |
| 4 | 2021-01-04 | 3 |
| 5 | 2021-01-05 | 3 |
| 6 | 2021-01-06 | 4 |
| 7 | 2021-01-07 | 5 |
| 8 | 2021-01-08 | 5 |
+---------+------------+------------+
In the above example, we have retrieved the data from the Customers and Orders tables. We can see that the Orders table has a foreign key called CustomerID that refers to the CustomerID field in the Customers table.
SQL Foreign Key is an important concept in database management. It ensures data consistency and integrity by creating relationships between tables. It is widely used in computer applications that require data storage and retrieval.