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

SQL Server 17 - ON DELETE and ON UPDATE

114 ratings | 4695 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 (11)
Samuel Ling (16 days 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
Alex Sav (3 months ago)
Thanks You are a good teacher
J P (9 months ago)
Wuzz up everyone! 👍 thank you for the explantion. Shout out from Arizona.
LuXxenatorX (1 year ago)
hello my name is jardip i am from delhi. i greatly appreciate the video you made, it is very good. i hope you get many pusy and bobs because you deserve it my friend
Mahadonation1 (1 year ago)
thanks for the help, hopefully your lungs aren't set to null
Kaz Tee (1 year 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 (1 year ago)
YOU ARE THE BEST!
Ian sberg (1 year ago)
glad you kept it in. HAHA
RussianVideoPodcast (1 year 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 (1 year 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 (1 year 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.