RI Constraints

Question 1

Example of defining RI constraint

Answer 1

CREATE TABLE Departments (
id INT PRIMARY KEY,
name VARCHAR(50)
);

CREATE TABLE Employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
–if the PKey in Departments (parent) changes, the FKey here will change
FOREIGN KEY (department_id) REFERENCES Departments(id) ON UPDATE CASCADE
);

Question 1

ON UPDATE CASCADE

Answer 1

“If the primary key in Table B (parent) changes, update the corresponding value in my foreign key column.”

Question 2

ON DELETE CASCADE

Answer 2

“If a row in Table B (parent) is deleted, delete my related rows automatically.”

Question 3

ON UPDATE SET NULL

Answer 3

“If the primary key in Table B (parent) changes, set my foreign key column to NULL”

Question 4

ON DELETE SET NULL

Answer 4

“If the primary key in Table B (parent) gets deleted, set my foreign key column to NULL”

Question 5

ON UPDATE SET DEFAULT

Answer 5

“If the primary key in Table B (parent) changes, set my foreign key column to its default value”

Question 6

ON UPDATE RESTRICT

Answer 6

“If you try to update a primary key in Table B (parent) that I reference, stop the operation if it affects me”

Question 7

ON UPDATE NO ACTION

Answer 7

“If the primary key in Table B (parent) changes, do nothing immediately, but enforce integrity later if needed”