SQL Reference - RESTRICT

RESTRICT

RESTRICT is a keyword in SQL that prevents the deletion of a table if there are any dependencies. It ensures data integrity by avoiding unintentional removal of referenced data. Upon a RESTRICT condition, SQL first checks for any foreign key references and aborts the operation if any dependencies are found.

Example

CREATE DATABASE TestDB;
USE TestDB;

CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(30),
    department_id INT,
    FOREIGN KEY(department_id) REFERENCES Departments(id)
    ON DELETE RESTRICT
);

INSERT INTO Employees
VALUES(1, 'John Doe', 1),
      (2, 'Jane Doe', 2),
      (3, 'Bill Smith', NULL);

CREATE TABLE Departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(30)
);

DROP TABLE Departments;

Output

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Explanation

The fOREIGN KEY constraint with RESTRICT option prevents from deleting the connected data in “Departments” table. So trying to drop the “Departments” table, which is referenced by “Employees” table, leads to an error.

Example

DROP TABLE employees RESTRICT;

Output

Table dropped.

Explanation

In this example, the “DROP TABLE” statement deletes the “employees” table. The “RESTRICT” clause ensures that the table is not dropped if it has dependencies, such as views or child tables. If such dependencies exist, the query will result in an error, thus preventing the table from being dropped. This safeguards against inadvertent deletion of tables that are needed elsewhere in your database.

Example

ALTER TABLE Employees
DROP COLUMN Email RESTRICT;

Output

Msg 3726, Level 16, State 1, Line 2
Could not drop object 'Employees' because it is referenced by a FOREIGN KEY constraint.

Explanation

The DROP COLUMN Email command was attempted on our Employees table using the RESTRICT keyword. SQL Server returned an error message because the Email column is referenced by a FOREIGN KEY constraint. The RESTRICT keyword disallows the operation if there are any dependencies. Thus, the operation fails due to the existing constraints.