I have two tables, in PostgreSQL 15.2:

CREATE TABLE first
(
  id serial INT NOT NULL,
  A INT NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE second
(
  id serial INT NOT NULL,
  f_id INT NOT NULL,
  B INT NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT fk_id
    FOREIGN KEY (f_id)
      REFERENCES first(id)
      ON DELETE CASCADE
);

I populated them quite simply:

INSERT INTO first VALUES (1);
INSERT INTO second VALUES (1,1);

I did a DROP TABLE first CASCADE; and I expected to get an empty second table, but the data is still there with a f_id even when first is empty.

Did I misunderstand how DROP ... CASCADE works or did I forget to declare something at creation time? I thought DROP is like a DELETE at a row level and CASCADE removes any foreign key constraint.

>Solution :

You need to distinguish between Data Definition Language (setting up and modifying the schema) and Data Modification Language (inserting, updating, and deleting data).

CREATE TABLE, DROP TABLE and CONSTRAINT belong the the DDL. If you drop the table, it removes the table (including its data) from the schema. This fails if the table is still referenced by other entities in the schema (like the foreign key constraint on the other table). If you drop the table with CASCADE, it also removes those other entities recursively. So you removed only the fk_id constraint, you did not modify the data of the second table.

I thought DROP is like a DELETE at a row level and CASCADE removes any foreign key constraint.

No. DELETE is the row-level DML statement for removing data from a table. Use DELETE FROM first;, it will delete all the values (but keep the table definition itself), and it will cascade to the referencing data in the second table.

You might have been looking for TRUNCATE first as well, possibly TRUNCATE first CASCADE which is the same as TRUNCATE first, second;.