I think you must be misunderstanding
CASCADE foreign keys, and/or the rules around foreign keys in general.
A foreign key can only refer to a table with a unique constraint or primary key. So in your example, you cannot create a foreign key referencing
table_b if there can be multiple instances of a value in the referenced column.
DELETE FROM ... a table, and another table has an
ON DELETE CASCADE foreign key reference to it, all referencing rows are deleted from the other table.
The column referenced cannot have multiple instances of the value, because you cannot create a foreign key to a column that is not
The referring column of the table with the constraint may have multiple values that refer to the column in the referenced table. If so, when the referenced value is removed, all these are deleted, as you would expect in a
regress=> CREATE TABLE table_b (id integer);
regress=> INSERT INTO table_b (id) VALUES (1), (2), (3), (1);
INSERT 0 4
Can't create the foreign key, no unique constraint or PK:
regress=> CREATE TABLE table_a (b_id integer REFERENCES table_b(id) ON DELETE CASCADE);
ERROR: there is no unique constraint matching given keys for referenced table "table_b"
Can't add a PK or unique constraint, has duplicates:
regress=> ALTER TABLE table_b ADD PRIMARY KEY (id);
ERROR: could not create unique index "table_b_pkey"
DETAIL: Key (id)=(1) is duplicated.