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


173 ratings | 7348 views
Now, what happens when you have successfully created a reference, and now you try deleting the parent? This is where the ON DELETE clause comes in. This is essentially how we configure how strict a foreign key is. There are 4 options you can give for an ON DELETE: Standard SQL Server NO ACTION Yes (Default) CASCADE Yes SET NULL Yes SET DEFAULT Yes Now the default is NO ACTION. This means that when you try to delete a parent row, SQL Server is going to throw an error and not let you. Cascade means that if you delete the parent row, any rows that reference that parent will also be deleted. This is pretty dangerous and not often recommended. SET NULL will set the foreign key to be NULL. Now obviously, this is going to require that the foreign key is not labelled NOT NULL. Finally, set DEFAULT will change the reference to the child row to some default value. We have not discussed defaults, but a column can have a default value. For example, we could make a deleted user in our Users table, and set the default for the foreign key to be the deleted user, and when any other users are deleted it will default to the deleted user UserId. In addition to the ON DELETE clause, there is the ON UPDATE clause. This one is a little less common because it configures what happens when a parent value changes. Obviously, when you are referencing a primary key, the ON UPDATE clause is nearly useless. That's because the primary key value is never supposed to change. If, on the other hand, we have a foreign key referencing a UNIQUE column that is not a primary key, it may change occasionally. So the only times you have to worry about the ON UPDATE clause is with foreign keys referencing UNIQUE columns that are not a primary key, and when you have natural keys that break the rules and actually do change at some point in time. The ON UPDATE clause has the same options as the ON DELETE clause and they all work the same way. Now, the last thing you need to know about foreign keys is that they must match the data type of the column you are referencing. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! 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 (16)
hatim lechqer (11 days ago)
Adam Bernard (1 month ago)
I just hate it when my DB explodes
hi william (2 months ago)
its better if your references always add arrow, anyway still a great series
ashish pant (5 months ago)
Yeah I thought she default is about for anonymous one
Glory Kat777 (6 months ago)
Great video, thanks a lot
Sameh Medany (6 months ago)
Very simple way..........Super Like
Samuel Ling (7 months ago)
i have a use case for default, though, it doesn't seems to be related to this video i use default for the user power in the usertable, i used it to differentiate between normal user, moderator and admin, like for example 0 = normal user, 1 = moderator and 2 = admin, so the default i put 0 since, it should be assigned to all user once they created their account
J P (1 year ago)
Wuzz up everyone! 👍 thank you for the explantion. Shout out from Arizona.
Mahadonation1 (2 years ago)
thanks for the help, hopefully your lungs aren't set to null
MBL 7au41 (2 months ago)
Kaz Tee (2 years ago)
can someone please help with this sql i want the user to update there profile. i create a profile page now i cant get the sql correct for the update button. i tried update user (table) set firstname,lastname,address,email,telephone (fields in my table) where 'email' (email it uses to update that particular user account details
Bourne H (2 years ago)
Ian sberg (2 years ago)
glad you kept it in. HAHA
RussianVideoPodcast (2 years ago)
Just great videos. Please keep them coming. I watch every day. Your style of delivering this info is very clear and easy to understand.
Zvi Twersky (2 years ago)
Great stuff! Question... if you have CASCADE set on a UNIQUE FK and try to change the value of the FK (on UPDATE) to a value that exists already in the table... will the UNIQUE attribute still kick in and not allow this, even though you have it set to Cascade? (makes sense that it would but I wanted to ask).
Ian sberg (2 years ago)
I dont know from top of my head but my suggestion would be to try it out. Build the tables and FK relationships and see what happens.

Would you like to comment?

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