ON DELETE cascade / ON DELETE set null

Khaled Alkhudari
A free video tutorial from Khaled Alkhudari
Sr Oracle Developer/ Sr system analyst
4.5 instructor rating • 7 courses • 24,668 students

Learn more from the full course

Oracle Database 12c SQL Certified Associate 1Z0-071

Become Oracle SQL Certified ( OCA ) / Covers ORACLE university track 100%. All the presentations & scripts are attached

19:53:19 of on-demand video • Updated January 2020

  • The Full track of Oracle Database 12c: Introduction to SQL Ed 2.
  • You will be prepared 100% to pass exams Oracle Database SQL 1Z0-071 And also exam Oracle Database 12c: SQL Fundamentals 1Z0-061
  • Very strong introduction to make you understand the oracle database and RDBMS.
  • Oracle Database 12c Architecture
  • Downloading and installing oracle database 12c in details
  • Connecting to the Database
  • Retrieving data using the select statement / Restricting and Sorting Data
  • Substitution Variables
  • Using Single-Row Functions to Customize Output
  • Reporting Aggregated Data Using the Group Functions
  • Displaying Data from multiple tables using joins
  • Using Subqueries to Solve Queries
  • Using the Set Operators
  • Managing tables using DML Statments ( Insert, Update, Delete )
  • Data Definition Language / Creating tables
  • Data Dictionary Views
  • Creating sequences, synonyms, and indexes
  • Creating Views in details
  • Managing Schema Objects
  • Retrieving Data By Using Subqueries
  • Manipulating Data By Using Subqueries
  • Controlling User Access in details
  • Manipulating Data
  • Managing Data in Different Time Zones
  • Exam information / Exams Details / Exam Practice
  • many other information, you can see the full curriculum
English [Auto] Hello everyone. Welcome back. You are doing a great job. We understand many things today. We will talk about the very important topic which is delete. Biggest Game On set up. What does it mean to understand this. Listen let's go do the exercises first thing we will understand something called on deletes cascade. Now let's execute. This is Dedman deleted from departments. I will try to execute this statement and it will give me integrity constraint and it will give me the name of validated Scheid the code. Because the department table is a master table. There is a Franki from table entries refer to this table because you cannot delete departments and there is already employees exist in employee table working in these department. So in order to make this statement you have to go first to the entry tables to delete all the employees then delete the department table. OK but Oracle give you a nice option called on delete Guscott. Now let's stick you to start doing this exercise our Ikaria table master local dept 1 and our create table called 8 MP 1 and I will make it exactly the same. Employees and department table. So this is a good exercise for you. Create table dept one dipped an all number Denia involve Carter to 100 and a the constraint dept one underscore pick a primary key you know. So a table with two columns and a mic dipped and or as a primary key. I will execute this to create on the table you created. Now I would insert a values on this table. I would ask two department department one. And then it is our dept and also Department to then it is be our dept and I will commit. So I would execute this script. One rule inserted one inserted comment completed. I will select a song from get one to make sure. So I have two departments Department one each. Our dept and department to be all Dept. Now our create table called MP 1 and I will make a foreign key from this table. Refer to the dept one so create table MP 1 MP ID number a primary key. So here I use a Coolum syntax to put the constraint on. I didn't mention any constraint him in this way. Oracle will never understand this underscores C and give it a number. OK very nice of practice and in Valcartier 100 dept and or number. And I make a constraint called one if care for. They know which is this column references to two on the table that you created up do dep't and or call them and a party here something called on delete cascade. So what is the purpose of this. I will tell you what is the purpose of this. So now I will create this table table created. So no I created two table that dept one is a master table and the ENP one is. Auditors table. Now I will insert into the empty table 5 records. Three employees working in Department 1 and 2 employee working in Department 2. So I will answer them one 1 too. Ali 3 Ahmadu working in Department 1. I would execute this statement and I will answer in 3 4 and 5 Ranja lot are working in Department 2 and I will commit I would select the Started from the table and pick one. And this is the result. So I have three employees walking in the park with one and two employees working in department two. So now when I make Drita from gift one where dipped and are equal one now it will not give me ill because I put here on daily it casket. So when I make this statement The Oracle server will go to the employee table and remove the record. Who is working in the pocket one then he will delete that department one. So it's a very nice. So now I would execute with this statement and one row deleted. Now if I select to start from MP 1 you will find that I have only four and five. So the empty one and two and three has been removed or deleted from the table. So this is only the head gasket. This means that when you delete the master record Oracle will go to the details record. In other tables and remove it. That's it. Another option we have on delete sit. I think that you understand this. So this means that when you try to delete a master table then Oracle will go to the details table and make the value enough so he will not delete. He will make it up. So now I will do the same exercise. I will make do with the same constraint. I would Essar two values and commit with the same. Then I would select the stuff. Do I have two departments 1 and 2. Then I would create table and be to and I will make the on foot in key differences to this table. But here I will put undelete set up so quickly at this table table you create it. Then I would answer the same values three employees working in department one and two employees working in department two. And I welcome it. I would execute the script then our query the table. So I have three employees working in department one and two employees working in department you know when I make three different dep't we're no equal one when I try to remove the record muster from departments what will happen. I really know the MP 2 and I will find that the Oracle make is that done as not. So this is the differences between on the Cascades and on the set up. So today we talk of very nice examples. Thank you for listening. I'll see you in the next video.