1

What is the reasoning behind the design that you can specify CASCADE after a DROP query (DML) like DROP users CASCADE; but not in a DELETE query (DDL) like DELETE FROM users WHERE user_id = 2 CASCADE;?

Example:

CREATE TABLE users (
 user_id integer PRIMARY KEY
);
CREATE TABLE comments (
  comment_id integer PRIMARY KEY,
  user_id integer REFERENCES users
);
INSERT INTO users (user_id) VALUES (1), (2);
INSERT INTO comments (comment_id, user_id) VALUES (11, 1), (21, 2), (22, 2);
DROP TABLE users;
-- ERROR: cannot drop table users because other objects depend on it
-- DETAIL: constraint comments_user_id_fkey on table comments depends on table users
-- HINT: Use DROP ... CASCADE to drop the dependent objects too.

DROP TABLE users CASCADE;
-- Query executed OK, 0 rows affected.
DELETE FROM users WHERE user_id = 2;
-- ERROR: update or delete on table "users" violates foreign key constraint "comments_user_id_fkey" on table "comments"
-- DETAIL: Key (user_id)=(2) is still referenced from table "comments".

DELETE FROM users WHERE user_id = 2 CASCADE;
-- ERROR: syntax error at or near "CASCADE"
-- LINE 1: DELETE FROM users WHERE user_id = 2 CASCADE

Instead, you have to define the CASCADE in the DML to be able to use it in DDL:

CREATE TABLE comments (
  comment_id integer PRIMARY KEY,
  user_id integer REFERENCES users ON DELETE CASCADE
);

DELETE FROM users WHERE user_id = 2;
-- Query executed OK, 1 row affected.
| improve this question | | | | |
  • 2
    This is unaswerable, I'm afraid. It's merely a decision that was made by the SQL standards people – Philᵀᴹ May 16 '19 at 14:17

Browse other questions tagged or ask your own question.