It"s in database systems subject to the DDL Part
And what if I don"t write any of them in my ON DELETE statement?
Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first)
The ON DELETE CASCADE và ON DELETE RESTRICT are the foreign key properties & you mix them when you create the relationship between two tables.
Bạn đang xem: On Delete Cascade là gì - Sự Khác Biệt Giữa On Xóa Cascade & On Update
If you set the relationship lớn be ON DELETE CASCADE, when you run a DELETE statement on a "parent" table, it will automatically DELETE all the corresponding rows from the "child" table. But the RESTRICT (which is the default foreign key relationship behavior) is when you try khổng lồ delete a row from the "parent" table & there is a row in the "child" table with the same ID, it will fail, complaining about the existing child rows.
Either way, you don"t need to lớn mention anything in your DELETE clause.
I also wrote a blog post about the different rules for DELETE và UPDATE commands in more detail here:
SQL vps Foreign Key Update and Delete Rules | Koukia
Share
Improve this answer
Follow
edited Jul 4, 2021 at 9:21
Goo
Dee
JAY
1,64522 gold badges1919 silver badges2626 bronze badges
answered Dec 16, 2013 at 19:40
Aram
Aram
5,49922 gold badges3030 silver badges4141 bronze badges
6
| Show 1 more comment
36
There are three types of on delete associated with foreign key
On Delete Cascade: when data is removed from a parent table, automatically data deleted from child table (foreign key table).On Delete mix Null: when data is removed from a parent table, the foreign key associated cell will be null in a child table.On Delete Restrict: when data is removed from a parent table, and there is a foreign key associated with child table it gives error, you can not delete the record.
Share
Improve this answer
Follow
edited Jun 19, 2017 at 14:49
ainex
322 bronze badges
answered Mar 13, 2017 at 22:44
Aditya Parmar
Aditya Parmar
1,1091313 silver badges2222 bronze badges
0
Add a comment |
1
CASCADE:Cascade is used khổng lồ delete or update the row that is referenced by the foreign key in the parent table. It will delete or update all the rows in the child table that reference the row in the parent table.
RESTRICT:Restrict is used to restrict the deletion or updating of the row in the parent table that is referenced by the foreign key in the child table. If there are any rows in the child table that reference the row in the parent table, the action (delete/update) will not be executed.
Example:
Table1
IDCategory
| 1 | A |
| 2 | B |
Table2
IDProduct
Category_ID
| 1 | Product1 | 1 |
| 2 | Product2 | 1 |
Table1 has a foreign key in Table2 (Category_ID).
CASCADE:If we delete row with ID 1 from Table1, then all the rows in Table2 with Category_ID 1 will be deleted.
RESTRICT:If we delete row with ID 1 from Table1, then the deletion will be restricted as there are rows in Table2 with Category_ID 1. The deletion will not be allowed.
Share
Improve this answer
Follow
edited Feb 10 at 10:14
buddemat
4,3591313 gold badges2525 silver badges4848 bronze badges
answered Feb 8 at 7:24
Unstopable
Unstopable
1122 bronze badges
Add a bình luận |
0
Right so these ON DELETE and ON UPDATE apply lớn the parent table/row. The reason NO action and RESTRICT are the same thing is because you cannot have a relationship with a child và a parent if the parent doesn"t exist.
Share
Improve this answer
Follow
answered Mar 25, 2014 at 0:30
elifareselifares
1
Add a bình luận |
Your Answer
Thanks for contributing an answer lớn Stack Overflow!
Please be sure khổng lồ answer the question. Provide details & share your research!But avoid …
Asking for help, clarification, or responding lớn other answers.Making statements based on opinion; back them up with references or personal experience.Xem thêm: Hướng Dẫn Chơi Board Game Bang, Luật Chơi, Cách Chơi Board Game Odd Chi Tiết
To learn more, see our tips on writing great answers.
Draft saved
Draft discarded
Sign up or log in
Sign up using Google
Sign up using Facebook
Sign up using thư điện tử and Password
Submit
Post as a guest
Name
Email Required, but never shown
Post as a guest
Name
Required, but never shown
Post Your Answer Discard
By clicking “Post Your Answer”, you agree lớn our terms of service, privacy policy và cookie policy
Not the answer you're looking for? Browse other questions tagged or ask your own question.
The Overflow Blog Featured on Meta
Related
5175
What is the difference between "INNER JOIN" và "OUTER JOIN"?
1668
What is the difference between UNION & UNION ALL?
1360
Difference between JOIN và INNER JOIN
876
What's the difference between identifying and non-identifying relationships?
1110
When should I use CROSS APPLY over INNER JOIN?
490
What are DDL and DML?
1566
What are the options for storing hierarchical data in a relational database?
Hot Network Questions more hot questions
Question feed
Subscribe khổng lồ RSS
Question feed to lớn subscribe khổng lồ this RSS feed, copy & paste this URL into your RSS reader.
lang-sql
Stack Overflow
Products
Company
Stack Exchange Network
Site thiết kế / hình ảnh © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. Rev2023.5.9.43421
Your privacy
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device & disclose information in accordance with our Cookie Policy.
For example, if I have two tables - Parent & Child - with a foreign key on Child that references Parent và has ON DELETE CASCADE, which records trigger a cascade & which records get deleted by the cascade? My first guess would be the Child records get deleted when Parent records are deleted, since Child records depend on Parent records, but the ON DELETE is ambiguous; it could mean delete the Parent record when the Child record is deleted, or it could mean delete the Child record when the Parent is deleted. So which is it?
I wish the syntax was ON PARENT DELETE, CASCADE, ON FOREIGN DELETE, CASCADE or something similar to remove the ambiguity. Does anyone have any mnemonics for remembering this?
Share
Improve this question
Follow
edited Jun 20, 2013 at 15:09
Johntron
asked Jun 20, 2013 at 14:18
Johntron
Johntron
1,70322 gold badges1111 silver badges77 bronze badges
1
Add a bình luận |
6 Answers 6
Sorted by: Reset to default
Highest score (default) Date modified (newest first) Date created (oldest first)
216
If you like the Parent và Child terms và you feel they are easy to be remembered, you may lượt thích the translation of ON DELETE CASCADE to lớn Leave No Orphans!
Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All children of the parent row are killed (deleted), too. If any of these children has grandchildren (in another table through another foreign key) và there is ON DELETE CASCADE defined, these should be killed, too (and all descendants, as long as there is a cascade effect defined.)
The FOREIGN KEY constraint itself could also be described as Allow No Orphans! (in the first place). No Child should ever be allowed (written) in the child table if it hasn"t a Parent (a row in the parent table).
For consistency, the ON DELETE RESTRICT can be translated to lớn the (less aggresive) You Can"t Kill Parents! Only childless rows can be killed (deleted.)
Share
Improve this answer
Follow
edited Sep 28, năm ngoái at 9:07
answered Jun 20, 2013 at 15:00
ypercubeᵀᴹypercubeᵀᴹ
96k1313 gold badges209209 silver badges302302 bronze badges
6
| Show 1 more comment
46
For example, if I have two tables - Parent & Child - where Child records are owned by Parent records, which table needs the ON DELETE CASCADE?
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.)
...it could mean delete the Parent record when the Child record is deleted, or it could mean delete the Child record when the Parent is deleted. So which is it?
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 vì chưng something to the "child" table. Cascading deletes is one thing you can do.
Chapter & verse, from Postgre
SQL docs.
Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO action means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you bởi vì not specify anything. (The essential difference between these two choices is that NO action allows the kiểm tra to be deferred until later in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: phối NULL và SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to lớn be set lớn nulls or their default values, respectively, when the referenced row is deleted. Note that these bởi not excuse you from observing any constraints. For example, if an action specifies SET mặc định but the mặc định value would not satisfy the foreign key constraint, the operation will fail.
