When to use ON UPDATE CASCADE

0 votes

I frequently use ON DELETE CASCADE, but I never use ON UPDATE CASCADE because I'm not sure when it will be useful.

Let's look at some code for the discussion's sake.

CREATE TABLE parent (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE child (
    id INT NOT NULL AUTO_INCREMENT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
);

If a parent with an id is deleted for ON DELETE CASCADE, a record in the child with parent id = parent.id will also be erased automatically. There should be no issue with this.

  1. This implies that ON UPDATE CASCADE will act in the same way when the parent's ID is modified, right?
  2. If (1) is correct, there is no need to use ON UPDATE CASCADE if parent.id cannot be updated or will never be modified, such as when it is set to AUTO INCREMENT or is always set to TIMESTAMP. Is that accurate?
  3. What other circumstance should we utilize ON UPDATE CASCADE in if (2) is false?
  4. What happens if I update the child.parent id to something that doesn't already exist? Will it then be destroyed by default?

I am aware that some of the aforementioned questions can be tested programmatically for understanding, but I also want to know if any of this is dependant on the database vendor or not. Can someone please help me solve this?

Aug 25, 2022 in Database by Kithuzzz
• 38,000 points
1,332 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
0 answers

How would I use excel to generate a large update sql statement?

I know there's a way to have ...READ MORE

Mar 30, 2022 in Database by Edureka
• 13,690 points
2,382 views
0 votes
0 answers

SQL Update from One Table to Another Based on a ID Match

Account and credit card numbers are stored ...READ MORE

Aug 22, 2022 in Database by Kithuzzz
• 38,000 points
1,240 views
0 votes
0 answers

Real life example, when to use OUTER / CROSS APPLY in SQL

With a colleague, I've been researching CROSS/OUTER ...READ MORE

Aug 25, 2022 in Database by Kithuzzz
• 38,000 points
812 views
0 votes
0 answers

I want to use CASE statement to update some records in sql server 2005

UPDATE dbo.TestStudents SET LASTNAME = ( ...READ MORE

Sep 2, 2022 in Database by Kithuzzz
• 38,000 points
1,246 views
0 votes
1 answer

Why is not preferable to use mysql_* functions in PHP?

The reasons are as follows: The MySQL extension: Does ...READ MORE

answered Sep 7, 2018 in Database by DataKing99
• 8,250 points
1,286 views
0 votes
1 answer

How to install Oracle DB on Windows?

Installing Oracle DB is pretty easy wrt other ...READ MORE

answered Feb 28, 2019 in Database by Priyaj
• 58,020 points
1,514 views
0 votes
0 answers
0 votes
1 answer

INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server

Your table dbo.Sup Item Cat refers to ...READ MORE

answered Sep 19, 2022 in Database by narikkadan
• 63,600 points
8,588 views
0 votes
1 answer

Which query to use for better performance, join in SQL or using Dataset API?

DataFrames and SparkSQL performed almost about the ...READ MORE

answered Apr 19, 2018 in Apache Spark by kurt_cobain
• 9,350 points
1,858 views
0 votes
1 answer

How to connect Java program to the MySQL database?

You can connect your Java code with ...READ MORE

answered May 11, 2018 in Java by Parth
• 4,640 points
1,932 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP