Database basic tutorial - part 15: Optional Join

These terms refer to the referential actions that can be specified in SQL when defining foreign key constraints. They dictate what happens to the related records in a child table when a record in the parent table is deleted or updated.

CASCADE: Automatically propagates changes (delete/update) from the parent to the child records.

CREATE TABLE IF NOT EXISTS Employees (

    id INT PRIMARY KEY AUTO_INCREMENT,

    `name` VARCHAR(30),

    department_id INT,

    salary FLOAT, FOREIGN KEY (department_id) REFERENCES Departments(department_id)     ON DELETE CASCADE

    ON UPDATE CASCADE

);

NO ACTION: Prevents the change if it would violate referential integrity.

CREATE TABLE IF NOT EXISTS Employees (

    id INT PRIMARY KEY AUTO_INCREMENT,

    `name` VARCHAR(30),

    department_id INT,

    salary FLOAT, FOREIGN KEY (department_id) REFERENCES Departments(department_id)     ON DELETE NO ACTION

    ON UPDATE NO ACTION

);

SET NULL: Sets the foreign key field to NULL when the parent record is deleted or updated.

CREATE TABLE IF NOT EXISTS Employees (

    id INT PRIMARY KEY AUTO_INCREMENT,

    `name` VARCHAR(30),

    department_id INT,

    salary FLOAT, FOREIGN KEY (department_id) REFERENCES Departments(department_id)     ON DELETE SET NULL

    ON UPDATE SET NULL

);

SET DEFAULT: Sets the foreign key field to its default value when the parent record is deleted or updated.

* Note: MySql is InnoDB, so you can't use ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT

CREATE TABLE IF NOT EXISTS Employees (

    id INT PRIMARY KEY AUTO_INCREMENT,

    `name` VARCHAR(30),

    department_id INT DEFAULT 1,

    salary FLOAT, FOREIGN KEY (department_id) REFERENCES Departments(department_id)     ON DELETE SET DEFAULT

    ON UPDATE SET DEFAULT

);

Example code:

SET SQL_SAFE_UPDATES = 0; USE JadeDb; DROP TABLE IF EXISTS Employees; DROP TABLE IF EXISTS Departments; CREATE TABLE IF NOT EXISTS Departments ( department_id INT PRIMARY KEY AUTO_INCREMENT, department_name VARCHAR(20), location VARCHAR(20) ); CREATE TABLE IF NOT EXISTS Employees ( id INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(30), department_id INT DEFAULT 1, salary FLOAT, FOREIGN KEY (department_id) REFERENCES Departments(department_id) ON DELETE SET NULL ); INSERT INTO Departments (department_name, location) VALUES ( 'HR', 'New York' ) , ( 'Engineering', 'San Francisco' ) , ( 'Sales', 'Chicago' ) ; INSERT INTO Employees (`name`, department_id, salary) VALUES ( 'Alice', 1, 70000 ) , ( 'Bob', 2, 80000 ) , ( 'Charlie', 3, 60000 ) , ( 'David', 3, 75000 ) , ( 'Eve', 2, 90000 ) ; DELETE FROM Departments WHERE department_id = 1; SELECT * FROM Employees

Tutorial Video: