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.

closed as off-topic by Philᵀᴹ, mustaccio, John Eisbrener, LowlyDBA, MDCCL May 16 at 18:18

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, mustaccio, John Eisbrener, LowlyDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    This is unaswerable, I'm afraid. It's merely a decision that was made by the SQL standards people – Philᵀᴹ May 16 at 14:17

Browse other questions tagged or ask your own question.