DROP TABLE CASCADE Drops Dependent Objects on PostgreSQL Only

A test teardown runs DROP TABLE customers CASCADE and moves on. PostgreSQL drops the foreign keys that point at the table along with it, MySQL and MariaDB ignore the word and refuse the drop, and SQLite reads CASCADE as a syntax error.

On PostgreSQL CASCADE drops the foreign keys and views that depend on the table along with it. MySQL and MariaDB read CASCADE and RESTRICT for compatibility and do nothing with them: a table that a foreign key refers to still can’t be dropped. SQLite has neither word.

DROP TABLE u CASCADE, run on four engines: SQLite 3.45.1 error: near "CASCADE": syntax error; PostgreSQL 16.15 no rows; MySQL 8.0.46 error: Cannot drop table 'u' referenced by a foreign key constraint 't_ibfk_1' on table 't'.; MariaDB 10.11.14 error: Cannot delete or update a parent row: a foreign key constraint fails.

What Each Engine Answered

On a table made with

CREATE TABLE u (id INTEGER PRIMARY KEY);
CREATE TABLE t (id INTEGER, u_id INTEGER, FOREIGN KEY (u_id) REFERENCES u (id));

the example

DROP TABLE u CASCADE;

gave these answers:

Engine	Answer
SQLite 3.45.1	error: `near "CASCADE": syntax error`
PostgreSQL 16.15	no rows
MySQL 8.0.46	error: `Cannot drop table 'u' referenced by a foreign key constraint 't_ibfk_1' on table 't'.`
MariaDB 10.11.14	error: `Cannot delete or update a parent row: a foreign key constraint fails`

Every Form BareSQL Checks

BareSQL Portable checks 2 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

Form	SQLite	PostgreSQL	MySQL	MariaDB	What to write instead
`CASCADE`	no	yes	yes	yes
`RESTRICT`	no	yes	yes	yes	Leave RESTRICT out: dropping a table that others depend on fails without it on PostgreSQL, MySQL and MariaDB.

What to Write Instead

This form answers the same on every engine:

DROP TABLE t;
DROP TABLE u;

Engine	Answer
SQLite 3.45.1	no rows
PostgreSQL 16.15	no rows
MySQL 8.0.46	no rows
MariaDB 10.11.14	no rows

Drop what depends on the table first (the tables or foreign keys that refer to it and the views that read it), then the table itself, without CASCADE.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:1: error: DROP TABLE ... CASCADE works on PostgreSQL, MySQL and MariaDB; SQLite refuses it. MySQL and MariaDB ignore CASCADE: a table a foreign key refers to still can't be dropped there, while PostgreSQL drops the foreign keys with it. [drop-cascade]
    DROP TABLE u CASCADE
    Drop what depends on the table first (the tables or foreign keys that refer to it and the views that read it), then the table itself, without CASCADE.

1 problem: 1 error.

baresql rules drop-cascade prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.