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

MySQL 17 - ON DELETE and ON UPDATE

390 ratings | 13593 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 come in. This is essentially how we configure how strict a foreign key is. There are 3 options you can give for an ON DELETE: RESTRICT / NO ACTION Yes - In MySQL they are the same, but not all DBMS. CASCADE Yes SET NULL Yes Now the default is RESTRICT. This means that when you try to delete a parent row, MySQL 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. 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 (28)
Jessica Tan (1 month ago)
I have the following SQL code for MySQL. ON DELETE SET NULL does not work. ON DELETE CASCADE and ON DELETE RESTRICT does. Anyone knows why? CONSTRAINT AppointmentRequestID_FK1 FOREIGN KEY (PatientNo) REFERENCES Happyville.Patient (PatientNo) ON DELETE SET NULL ON UPDATE CASCADE,
mehran nasr (2 months ago)
thank u so muchh!
Uh Gosh, thanks God you exist, it is hard to find someone NOT indian explaining how those topics on YouTube!
venkat dudala (3 months ago)
what happens when a config file is deleted unfortunately ? whether mysql will run or stop? can anyone help me please
Andrey Doichinov (3 months ago)
keep doing this, it's what you are born for :)
Ashwani Garg (5 months ago)
Best tutorial on SQL Foreign Key Clauses, Thank You for sharing this important information in such an easy way.
TiR3Dnow (6 months ago)
This video is amazing. I looked on quite a few forums and stackoverflow but your way of explaining this is by far the best. Thanks man.
Juan Joya (6 months ago)
thanks a lot, it was excatly what I needed
SetoSokotsu (9 months ago)
...doing god's work...
Govind Yatnalkar (9 months ago)
Awesome!!!!
JakeVilMac (9 months ago)
Thanks. That was a simple and clear explanation :)
동재짜응 (9 months ago)
Yea Excellent Bruh....Man Ur the best
André Gomes (10 months ago)
Caleb great videos
Marta Siemiona (11 months ago)
Excellent video! Thank you!
László Monda (1 year ago)
You make learning fun!
Sabbir Ahmed (1 year ago)
Is it possible to delete multiple rows with same foriegn key upon deleting the PK where those Fk were pointing to
Sabbir Ahmed (1 year ago)
If a PK is deleted, will all the rows (Fk) pointing to that PK will be deleted?
Daniel Krueger (1 year ago)
It's a great video... I would add a short comment on where to put the ON DELETE... Mainly because when you are talking about create table you might not be describing this concept. As a new learner I suspect that you put the ON DELETE in the child table, but I'm not quite sure like so. --> CONSTRAINT FK_parentID FOREIGN KEY (`parentid`) REFERENCES `parent`(`id`)
Serghei Tricolici (1 year ago)
Great video, 10x!
أمجاد (1 year ago)
thank u so much u saved me i have a midterm exam tmw
The Unknown Evidence (1 year ago)
Excellent video.
Anant Mishra (1 year ago)
Thnx, yo.
Amir motahar (1 year ago)
Hello Caleb I was watching your old video about on update and on delete but it was a little cofusing but it is more clearer thank you ! And how can we add on delete and on update together in a table for a foregin key ??
Mers Mer (1 year ago)
Thank you!!!
ミランダアダン (1 year ago)
Nice explanation! thanks a lot!
katana767 (1 year ago)
Upon deleting the 'parent row', the 'child row' is refereed to as the 'bat row'.
Cameron Chardukian (1 year ago)
56 people enjoyed, only 1 thought it was a total piece of garbage :))
Hector Oliveros (1 year ago)
Excellent explanation! Thanks

Would you like to comment?

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