Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having trouble understanding Postgres's CASCADE DELETE behavior.

If table_A REFERENCES table_B, and the column referenced could have multiple instances of the same value, will the table_A row be DELETEd even if there are still valid references upon a table_B DELETE?

If so, how can table_A only be DELETEd by CASCADE when there are no valid references left?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

When you 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 unique.

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 CASCADE.

regress=> CREATE TABLE table_b (id integer);
CREATE TABLE
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.
share|improve this answer
1  
Thank you! lol, yes, I was getting lost in my own spaghetti, and being a noob doesn't help either. –  Cincinnatus Feb 12 at 13:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.