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

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