ADVERTISEMENT

SQL FOREIGN KEY

A FOREIGN KEY creates a link between two tables by enforcing a relationship. It ensures the value in one table matches a value in another, maintaining referential integrity.

🔹 Basic Syntax

-- Define foreign key when creating a table
CREATE TABLE child_table (
  column_name data_type,
  FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
);

-- Add foreign key to existing table
ALTER TABLE child_table
ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table(parent_column);

🔹 Example

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(100)
);

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(100),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

🔹 Important Notes

  • FOREIGN KEY values must match a value in the referenced (parent) table or be NULL.
  • Helps prevent orphan records and maintains consistent data.
  • You can specify actions like ON DELETE CASCADE to automatically delete related rows.
  • Multiple foreign keys can exist in a table.
  • Parent table must have a PRIMARY KEY or UNIQUE constraint on the referenced column.

🧠 Quick Recap

Key PointExplanation
FOREIGN KEYLinks columns between tables, enforcing relationship
Referential IntegrityEnsures child value exists in parent table
Actions on Delete/UpdateCan cascade or restrict changes
Multiple FKs allowedA table can have multiple foreign keys
Requires parent keyParent column must be PRIMARY KEY or UNIQUE

💡 Use FOREIGN KEYS to enforce data consistency across related tables and maintain relational integrity.

ADVERTISEMENT