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.
CREATE TABLE projects(
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
More content: http://CalebCurry.com
Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)