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.
