🗄️ SQL Q12 / 104

What is a foreign key?

AI-Powered Answer ✓ Answered

A foreign key is a column or a set of columns in a database table that refers to the primary key of another table. It establishes a link between two tables, enforcing referential integrity and maintaining the consistency of data.

What is a Foreign Key?

A foreign key acts as a cross-reference between tables. It's a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the primary key it refers to is called the parent or referenced table.

Purpose of a Foreign Key

Enforces referential integrity: Ensures that relationships between tables remain consistent.
Prevents actions that would destroy links between tables (e.g., deleting a parent row when child rows still reference it).
Maintains data consistency and accuracy across related tables.
Facilitates efficient data querying and joining operations.

How Foreign Keys Work

When you define a foreign key, you're essentially telling the database management system (DBMS) that the values in this column (or columns) in the child table must match values in the primary key column (or columns) of the parent table. This creates a parent-child relationship, where the child table's data is dependent on the parent table's data.

Syntax Example

sql

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

Key Characteristics

References a primary key or a unique key in another table.
Can have NULL values (unless explicitly defined as NOT NULL), meaning a child record might not have a parent.
Can be composed of one or more columns.
Multiple foreign keys can exist in a single table, linking it to several other tables.

Constraints and Actions

Foreign keys can be configured with actions that specify what happens to child records when the referenced parent record is deleted or updated. These are known as referential actions or constraints.

Action	Description
ON DELETE CASCADE	If a row in the parent table is deleted, corresponding rows in the child table are also deleted.
ON UPDATE CASCADE	If the primary key in the parent table is updated, the foreign key in the child table is also updated.
ON DELETE SET NULL	If a row in the parent table is deleted, the foreign key column(s) in the child table are set to NULL.
ON UPDATE SET NULL	If the primary key in the parent table is updated, the foreign key column(s) in the child table are set to NULL.
ON DELETE RESTRICT	Prevents deletion of a parent row if there are dependent child rows (default behavior for many databases).
ON UPDATE RESTRICT	Prevents update of a parent primary key if there are dependent child rows.

← All SQL questions