HomeНаука и техникаRelated VideosMore From: Caleb Curry

Oracle SQL Tutorial 19 - ON DELETE (SET NULL and CASCADE)

267 ratings | 19846 views
Welcome everyone! Something you need to consider when you are creating foreign keys is what happens if you delete the parent? As a reminder, the parent is the row that has the value you are referencing in the row that has a foreign key. Why is this something important to consider? It's important because foreign keys need to protect us from two primary things, unacceptable INSERT statements, and unacceptable DELETE statements. Let's see what happens when we try to insert incorrect data into the table with the foreign key: INSERT INTO projects VALUES (1, 'Update website homepage', 'CalebCurry') The response tells us plainly that there is no such user in the users table. So this works correctly. Deleting data on the other hand works a bit differently because the database does not know what you want to do with the child row when you delete the parent from the parent table. By default, we will get an error message that prevents the parent from being deleted, but there are some other options. How do we configure this? This is where the ON DELETE statement comes in. We add the keywords ON DELETE right after the foreign key and then we can give it the option of CASCADE or SET NULL. CASCADE means that if we delete the parent, we are also going to delete the child. In our situation what that means is that if somebody creates a project in our project table and then that persons account gets deleted, all of the projects he owns will also be deleted. CASCADE: CREATE TABLE projects( project_id NUMBER, Project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL, CONSTRAINT projects_pk PRIMARY KEY (project_id), CONSTRAINT projects_users_fk FOREIGN KEY (creator) REFERENCES users (username) ON DELETE CASCADE ) SET NULL will take the value in the child table and get rid of it. What you are left with is NULL. This means that we have an orphaned child. The first thought you might have is that it is a bad thing to have an orphaned child, but in databases that is not always so. In our application if we had it set to SET NULL, when a user account gets deleted the projects would remain in existence they would just lack a creator. This might be a good thing if you are concerned about the long term survival of a project, this might be the route you want to go. It ultimately depends on the application purpose. If you don't like CASCADE or SET NULL, you can leave the entire ON DELETE statement and just have Oracle throw an error when a parent is deleted. As for us, we are going to use ON DELETE CASCADE. We need to use this with extreme caution. If you are not careful, someday you will run a delete a row and that will cascade through you database deleting a bunch of stuff you didn't want to delete. Stuff happens, so make sure you back up your database every once in eternity. Now, in the last video we started with a database design that had three tables. We've only created two in this video. In the next video we are going to create the next one, which is a little special. Then we'll finish things up by adding some indexes. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me on Patreon! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Html code for embedding videos on your blog
Text Comments (13)
Roger Danuarta (26 days ago)
how to make update for foreign key ?
Arub (1 month ago)
thanks a lot
Siddharth g (4 months ago)
What's the alternate to UPDATE Cascade in Oracle ?
Sebastián Zunino (1 year ago)
I don't understand why creators is referenced to user_id and why I can't delete user_id with DELETE, I mean, creator is a FK of username, not user_id
ancient child (1 year ago)
not sure if i get your question right, but if i do, the answer is because if you deleted the user_id then the reference to user_id would be invalid and might cause errors in your application
kareem jeiroudi (1 year ago)
How to view the created tables 😅?
Sebastián Zunino (1 year ago)
Connections (on the left side) > Click on the name of your work > Tables > Search the name of the table and double click
Cube Smithy (1 year ago)
I hope that you get more popular Caleb; these videos are very easy to follow and helpful!
Shy Dee (2 years ago)
Hi Caleb, I am new to SQL and am trying my best to learn online. I have one question... example: productID and SupplierID are primary keys in their respective tables. Both have NOT NULL attributes as primary keys. Can the productID be changed to NULL if it is a foreign key in the supplier table ? So if a table has a list of items and has the supplierID missing how does one address this? Do you think you can explain this to me? Thanks!
Rahul Mahalanabis (2 years ago)
Rahul Mahalanabis (2 years ago)
+CalebTheVideoMaker2 THNX buddy
Caleb Curry (2 years ago)
Good luck!!
Pratyush Singh (2 years ago)
man your explanations are just awesome!!!! can you start java language.that would be really helpful..........

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.