SQL Foreign Keys

A Foreign Key is a column or a set of columns in one table that uniquely identifies a row of another table. The foreign key constraint is used to link two tables together.

It ensures referential integrity, meaning that a value in the foreign key column(s) must exist in the referenced primary key column(s) of the parent table, or be NULL (if allowed).

Purpose of Foreign Keys

• Enforce Data Integrity: Prevents invalid data from being entered into the database. For example, you can't assign an order to a customer that doesn't exist.
• Establish Relationships: Clearly defines the relationships between tables, which is crucial for complex database designs.
• Facilitate Data Navigation: Makes it easier to join tables and retrieve related information.

Syntax for Creating Foreign Keys

When Creating a Table:

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

Adding to an Existing Table:

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerOrder
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Key Concepts

Referenced Table (Parent Table)

This is the table that the foreign key points to. It typically contains the primary key that the foreign key column references.

Referencing Table (Child Table)

This is the table that contains the foreign key column(s).

Foreign Key Constraints Actions

When defining a foreign key, you can specify actions to take when data in the parent table is modified. These actions ensure consistency across related tables.

ON DELETE Actions:

• CASCADE: If a row in the parent table is deleted, the corresponding rows in the child table are also deleted.
• SET NULL: If a row in the parent table is deleted, the foreign key column(s) in the child table are set to NULL. (Requires the foreign key column to be nullable).
• NO ACTION (or RESTRICT): Prevents the deletion of a row in the parent table if there are matching rows in the child table. This is often the default behavior.

ON UPDATE Actions:

• CASCADE: If the primary key value in the parent table is updated, the corresponding foreign key values in the child table are also updated.
• SET NULL: If the primary key value in the parent table is updated, the foreign key column(s) in the child table are set to NULL. (Requires the foreign key column to be nullable).
• NO ACTION (or RESTRICT): Prevents the update of a primary key value in the parent table if there are matching rows in the child table.

Example with Actions:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255)
);

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Best Practices

• Always use foreign keys to link tables where a relationship exists.
• Choose appropriate ON DELETE and ON UPDATE actions based on your application's requirements.
• Ensure that data types of the foreign key columns match the data types of the referenced primary/unique key columns.
• Document your foreign key constraints and their behavior.