SQL FOREIGN KEY Keyword

 The FOREIGN KEY keyword in SQL is used to define a relationship between two tables. It establishes a link between a column (or a set of columns) in one table and the primary key in another table. This enforces referential integrity, ensuring that the data in the foreign key column matches data in the referenced primary key column.

Key Points:

• A foreign key in a child table points to a primary key in a parent table.
• A foreign key ensures that the value in a column or set of columns corresponds to values in another table.
• You can also specify actions like ON DELETE or ON UPDATE to control what happens when the referenced data changes or is deleted.

---

Syntax:

When Creating a Table:

CREATE TABLE child_table (
    column_name data_type,
    FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Adding a Foreign Key to an Existing Table:

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table(parent_column)
ON DELETE CASCADE
ON UPDATE CASCADE;

---

Example:

Parent Table:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50) NOT NULL
);

Child Table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

In this example:

• DepartmentID in Employees is a foreign key referencing DepartmentID in Departments.
• If a department is deleted, the DepartmentID in Employees is set to NULL.
• If the DepartmentID in Departments changes, the update cascades to Employees.

---

Constraints:

1. The data types of the foreign key and the primary key must match.
2. The referenced column in the parent table must be a primary key or unique.

Let me know if you need further explanation or more examples!