SQL - Foreign Key

In SQL, a FOREIGN KEY is a constraint that establishes a relationship between two tables based on a column or a set of columns. It ensures referential integrity by enforcing the following rules:

• The values in the foreign key column(s) of the referencing table must exist in the referenced table's primary key or unique key column(s).
• A foreign key value can be NULL or match a value in the referenced table.

Foreign key constraints help maintain consistency and integrity between related tables.

The foreign key is used to establish relationships between tables, reflecting the association between their data. It enables the enforcement of data integrity rules, such as ensuring that values in the referencing table match valid values in the referenced table.

Syntax:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
    FOREIGN KEY (column1, column2, ...)
    REFERENCES referenced_table (referenced_column1, referenced_column2, ...)
);

Alternatively, you can add a foreign key constraint to an existing table using the ALTER TABLE statement:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ...)
REFERENCES referenced_table (referenced_column1, referenced_column2, ...);

Example:

Let's consider two tables: Orders and Customers. The Orders table has an OrderID column, while the Customers table has a corresponding CustomerID column. We can establish a foreign key relationship between the two tables using the CustomerID column as the foreign key.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100)
);
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this example, the CustomerID column in the Orders table is defined as a foreign key, referencing the CustomerID column in the Customers table. This foreign key constraint ensures that the values in the CustomerID column of the Orders table match the existing CustomerID values in the Customers table.

By establishing this foreign key relationship, we gain several benefits:

• Referential integrity: The foreign key constraint prevents inserting or updating records in the Orders table with CustomerID values that do not exist in the Customers table.
• Relationship maintenance: The foreign key constraint helps maintain the association between orders and customers, allowing for reliable data retrieval and updates.
• Cascading actions: Foreign key constraints can define cascading actions, such as cascading deletes or updates, which automatically propagate changes to related records.

Foreign key constraints play a crucial role in maintaining data integrity and establishing relationships between tables in a SQL database. They ensure the consistency and accuracy of data across related tables.