Hackforge Academy

Explanation

A foreign key in a database is a column (or set of columns) in one table that refers to the primary key in another table. It is used to establish a relationship between two tables.

Code:

-- Example
CREATE TABLE Department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES 
Department(dept_id)
);

Explanation

A foreign key ensures referential integrity, meaning the value in the child table must exist in the parent table. You cannot insert invalid or non-existing references.

Code:

-- This will fail if dept_id does not exist
 in Department
INSERT INTO Employee VALUES (1, 'John', 10);

Explanation

Foreign keys are needed to maintain consistency between related tables and to prevent orphan records (records without a valid reference).

Code:

-- Prevent deleting parent record if 
child exists
DELETE FROM Department WHERE dept_id 
= 1; -- may fail if referenced

Explanation

They also support cascading actions like ON DELETE CASCADE or ON UPDATE CASCADE to automatically update or delete related records.

Code:

-- Cascade example
FOREIGN KEY (dept_id) 
REFERENCES Department(dept_id)
ON DELETE CASCADE;

Explanation

Real-time usage: Foreign keys are widely used in applications like e-commerce (orders linked to users), banking (transactions linked to accounts), and school systems (students linked to classes).

Code:

-- Example
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id)
 REFERENCES Users(user_id)
);