I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is which. For example, if I have two tables - I wish the syntax was asked Jun 20, 2013 at 14:18
JohntronJohntron 1,6232 gold badges11 silver badges7 bronze badges 1 If you like the Which means that when a The For consistency, the answered Jun 20, 2013 at 15:00
ypercubeᵀᴹypercubeᵀᴹ 94.6k13 gold badges199 silver badges296 bronze badges 6
ON DELETE CASCADE is an optional clause in a foreign key declaration. So it goes with the foreign key declaration. (Meaning, in the "child" table.)
One way to interpret a foreign key declaration is, "All valid values for this column come from 'that_column' in 'that_table'." When you delete a row in the "child" table, nobody cares. It doesn't affect data integrity. When you delete a row from the "parent" table--from "that_table"--you remove a valid value from the possible values for the "child" table. To maintain data integrity, you have to do something to the "child" table. Cascading deletes is one thing you can do. Chapter and verse, from PostgreSQL docs.
answered Jun 20, 2013 at 14:35
2 SQL:2011 SpecThere are five options for
The foreign key establishes the dependent relationship. The Example / Metaphor / ExplanationFor this example, we will accept the common model of society and economy: where every
If all
You have a few options here,
answered Jul 25, 2018 at 21:42
Evan CarrollEvan Carroll 58.4k41 gold badges216 silver badges439 bronze badges 2 A simple mnemonic would be ON DELETE of parent CASCADE [by deleting] here That tells you which deletes (deletes of the parent) get cascaded, where the ON DELETE CASCADE statement goes (on the child), and what gets deleted (the child). answered May 28, 2019 at 16:45
msouthmsouth 1413 bronze badges Just remember: Relational databases are all about consistency. They want to ensure the data is consistent all the time. If you have an The DB will become active when an author is deleted. When an author is deleted, the Book table could reference an ID that does no longer exist. Meaning that a deletion of an Author might need a "cascading" deletion of a book. answered Dec 13 at 8:31
well, perhaps we can rationalize the syntax. Let's take a Python example:
what this line says is on_delete of the Parent (which is accidentally mentioned in the statement), please cascade the deletion onto the child. That is why the CASCADE statement is defined at the child level, it marks those children that need to be deleted For example if you had another class
this structure would clearly show which of the children need to be removed (Child) and which are to stay (GrownUpChild) albeit orphaned [Edit: Given the context of the discussion, specifically in cases of on_delete=models.CASCADE etc, ] in fact it is often a desired behavior to leave children of a deleted parent, due to auditing and reporting reasons, as well as recovering accidental deletions. [ of course enterprise level software will be built around such behavior and will flag deleted records as deleted=1 instead of actually deleting them and also will not include them in any queries for the front end, minus some specially designed reports. In addition it will have a function of purging the deleted==1 records from the database, which will usually be executed by the UI administrator, often avoiding any involvement from the database administrator's side.] answered Jul 20, 2018 at 5:59
4 |