SQL FOREIGN KEY Constraint

A FOREIGN KEY constraint in SQL is used to enforce a link between the data in two tables. It ensures that the value in a column (or a set of columns) in one table matches the value in a column (or a set of columns) in another table. This maintains the referential integrity between the tables.

Example Scenario

Let's consider a simple example involving two tables:

1. Customers Table: This table stores customer information.
2. Orders Table: This table stores order information and references the Customers table.

Table Definitions

1. Customers Table

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ContactEmail VARCHAR(100)
);

2. Orders Table

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Explanation

• Customers Table:

  • CustomerID is the primary key.
  • CustomerName and ContactEmail are additional attributes.

• Orders Table:

  • OrderID is the primary key.
  • OrderDate is the date when the order was placed.
  • CustomerID is a foreign key that references CustomerID in the Customers table.

Example Insertions

1. Inserting Data into Customers

INSERT INTO Customers (CustomerID, CustomerName, ContactEmail)
VALUES (1, 'Alice Smith', 'alice.smith@example.com'),
       (2, 'Bob Johnson', 'bob.johnson@example.com');

2. Inserting Data into Orders

INSERT INTO Orders (OrderID, OrderDate, CustomerID)
VALUES (101, '2024-07-25', 1),  -- References CustomerID = 1
       (102, '2024-07-26', 2);  -- References CustomerID = 2

Output Verification

1. Query to Retrieve Data from Customers

SELECT * FROM Customers;

Output:

CustomerID | CustomerName | ContactEmail
-----------|--------------|----------------------
1          | Alice Smith  | alice.smith@example.com
2          | Bob Johnson  | bob.johnson@example.com

2. Query to Retrieve Data from Orders

SELECT * FROM Orders;

Output:

OrderID | OrderDate  | CustomerID
--------|------------|-----------
101     | 2024-07-25 | 1
102     | 2024-07-26 | 2

Referential Integrity

The foreign key constraint ensures that:

• You cannot insert a record into the Orders table with a CustomerID that does not exist in the Customers table.
• If a record in the Customers table is deleted, the corresponding records in the Orders table will be handled based on the configured behavior (CASCADE, SET NULL, NO ACTION, etc.).

For example, if you try to insert an order with a CustomerID that doesn't exist, the database will reject it, enforcing data consistency.