SQL RESTRICT

What is SQL RESTRICT?

RESTRICT prevents a DROP, DELETE, or UPDATE action when dependent objects or rows exist.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RESTRICT: Supported: PostgreSQL, Oracle, DB2, Teradata, SQL Standard compliant engines. Partial support: SQL Server (only ON DELETE/UPDATE), MySQL (only ON DELETE/UPDATE, no DROP RESTRICT). Not supported: SQLite DROP RESTRICT, but referential RESTRICT works via NO ACTION equivalence.

SQL RESTRICT Full Explanation

RESTRICT is a safeguard keyword that can follow certain SQL statements to block the requested action if it would break referential integrity or invalidate dependent objects. In Standard SQL you can append RESTRICT to DROP statements (DROP TABLE|VIEW|SCHEMA … RESTRICT) or define it as a referential action in a FOREIGN KEY constraint (ON DELETE RESTRICT / ON UPDATE RESTRICT). Some dialects (DB2, Teradata, Informix) also accept RESTRICT with GRANT and REVOKE. When the database engine encounters RESTRICT it performs an immediate check for dependencies. If any child rows or objects rely on the target, the statement fails with an error rather than implicitly removing or changing the children. Unlike NO ACTION, which defers the check until the end of the statement, RESTRICT enforces the rule immediately, making the difference visible only inside complex transactions with deferred constraints. RESTRICT is the opposite of CASCADE, which automatically deletes or drops dependents. It provides a safe-by-default option for administrators who prefer to refactor dependencies manually.

SQL RESTRICT Syntax

-- As part of a DROP statement
DROP TABLE table_name RESTRICT;
DROP VIEW view_name RESTRICT;
DROP SCHEMA schema_name RESTRICT;

-- As a referential action in a FOREIGN KEY
CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

SQL RESTRICT Parameters

Example Queries Using SQL RESTRICT

-- 1. Attempt to drop a table that has a referencing foreign key
DROP TABLE orders RESTRICT;  -- Fails if 'order_items' still references it

-- 2. Foreign key that blocks parent deletion while children exist
CREATE TABLE customers (
    id INT PRIMARY KEY
);

CREATE TABLE invoices (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id) ON DELETE RESTRICT
);

DELETE FROM customers WHERE id = 42;  -- Fails if invoices for customer 42 exist

Expected Output Using SQL RESTRICT

DROP TABLE … RESTRICT raises an error like "ERROR: cannot drop table orders because other objects depend on it" when dependencies are present.
DELETE FROM customers … fails with a foreign-key violation if related invoices exist; no rows are removed.

Use Cases with SQL RESTRICT

Prevent accidental loss of dependent data when cleaning up schemas
Enforce manual review before dropping production tables or views
Guarantee that parent records cannot be deleted while child records still reference them
Provide an immediate integrity check inside long transactions

Common Mistakes with SQL RESTRICT

Assuming RESTRICT will silently ignore the command; it actually throws an error.
Confusing RESTRICT with NO ACTION; NO ACTION checks at commit time, RESTRICT checks immediately.
Forgetting that some databases default to NO ACTION when RESTRICT is omitted.
Trying to use RESTRICT with statements or dialects that do not support it (e.g., MySQL DROP does not accept RESTRICT).

First Introduced In

SQL-92

Frequently Asked Questions

What does SQL RESTRICT do?

RESTRICT stops a DROP, DELETE, or UPDATE statement if the target has dependent objects or rows, raising an error instead of removing data.

When should I use RESTRICT instead of CASCADE?

Use RESTRICT when you want to guarantee manual review before deleting or altering objects, preventing accidental data loss.

Does MySQL support DROP TABLE ... RESTRICT?

No. MySQL supports RESTRICT only as a referential action in foreign keys. DROP statements ignore the keyword.

How is RESTRICT enforced inside a transaction?

RESTRICT performs the dependency check immediately. If the check fails, the statement is rolled back on the spot, even if the transaction remains open.