Q
Problem solve Get help with specific problems with your technologies, process and projects.

DELETE or UPDATE, CASCADE or RESTRICT, part 2

Here is the continuation of Rudy's answer. See part 1.

Here again is the sample data introduced in the first part of this answer:

 Team Member Person 111 Alpha 111 JJ JJ John 222 Beta 111 LL KK Karl 333 Gamma 222 KK LL Linda 444 Delta 222 MM MM Mary 333 JJ 333 LL 333 MM


As for changing the associations, there are two types of changes to consider. The first is straightforward, and involves changing the value of a primary key. If for some reason you wanted to change Karl's primary key from KK to something else, all you need to do is just update the Person table, and the action will be either cascaded or prohibited, depending on whether you specified ON UPDATE CASCADE or ON UPDATE RESTRICT. Note that RESTRICT is the default that comes into effect whenever a foreign key is declared. Since there are no ON UPDATE clauses in the above example, you cannot change Karl's primary key.

The other change to an association is also straightforward. Suppose Linda wants to leave Alpha and join Beta. The application logic that implements this change can choose between these strategies dealing with the Member table:

  1. update the existing row 111LL to 222LL

  2. delete the existing row 111LL and insert a new row 222LL

In both of these cases, relational integrity will be enforced to ensure that the updated or new Member row has valid foreign key values to both Team and Person tables.

So everything hinges on declaring foreign keys. If you didn't have the foreign keys declared, then you could delete Mary from the Person table and yet leave MM rows in the Member table, or you could change Karl's primary key to something else and yet leave KK rows in the Member table.

Always declare foreign keys, and if the business logic of the application allows it, go ahead and declare ON DELETE/UPDATE CASCADE actions where appropriate.


This was last published in November 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close