SQL session 5 | Foreign Key Constraint - Complete Demonstration | Trendytech

[@sumitmittal07]

Checkout the Big Data course details here: trendytech.in/?referrer=youtube_sql6

[@pridename2858]

Hi guys, its shame that this wonderful SQL series has around 2k thumbs up only. Remember to this Teacher Mr Sumit..he input his soul, hard core efforts by heart, he is trying to work for your success for your career progression by providing this specific skill free of cost. Guys, please be honest to yourself and make this 2k to 200K,I believe it worth more than that. I am earning 100K in $ just because this guy Mr. Sumit, no brainer you are earning or soon will get more than me. Hope you will respect Mr. Sumit genuine efforts for you.

[@mmartel02]

Recommendation for students following along. At this point in the course, you should avoid dropping and recreating tables when altering the structure of your tables - this isn't a good representation of what you'd do in a real world setting. Take this opportunity to practice your ALTER/ADD/DROP/MODIFY/CHANGE commands to make the changes needed to your existing table. For Example, you can add a foreign key to the selected_course column with the following command: ALTER TABLE students ADD FOREIGN KEY (selected_course) REFERENCES courses(course_id); Thank you for the great content Sumit!

[@navneet2649]

First of all thanks so much for ur comment. It really helps to know different perspective! 👍👍💖💖

[@navneet2649]

@21:05 there is a issue which I am facing while going for what u said, here we are wanting to add a column named 'selected_course' in the table, but if I use the " *ALTER* TABLE students *ADD* COLUMN selected_course INT;" then the system is adding this new column in the last of my table, but if we go for DROP TABLE and CREATE TABLE then we can decide where to place this new column, so pls guide!

[@navneet2649]

In Session 4 of this course, we have learned abt ADD, DROP and MODIFY but then what is CHANGE, it didnt come till now!! ??

[@mmartel02]

Use AFTER {column_name} after your ADD COLUMN command

[@mmartel02]

Modify and change have the same function except change allows you to rename the column whereas modify does not

[@asifbhat3796]

this is called real teaching. Other big tech education giants are just reading from the book and paste it on slides .

[@priyankachoudhary934]

One of the best explanation of Foreign Key. Cleared the vision of FK.

[@venkataabhilash5356]

This session will say how you different from other trainers..really nice explaination

[@sumitmittal07]

Thank you and hope you would like the upcoming videos as well :)

[@mdhasib1844]

Thank you sumit sir for your effort. Its been 6 days since I am watching your SQL series and I would say; your teaching style is fantastic.

[@UMERohanVerma]

Sir I really appreciate the amount of hard work done and time spent to put all these concepts and materials together and creating this playlist. Hats off

[@idiotengineer1012]

It's really good session that mean person will realise what is SQL and how it works... I specifically suggest to all watch this playlist of SQL to learn..

[@govindsinghtomar2003]

Worth it to Watch. Great Explanation by Sumit Sir.

[@ramprajapati-j8j]

Very useful session however 7 seven years i came to know the actual things on foreign key. Thanks a lot Sumit Sir.

[@jimitshah7636]

Best explanation of foreign key available on the internet!

[@abhishekkumar-sj2jj]

very good explanation of foreign key and it's role

[@optionkingrobot]

Really ................ Awesome feeling at end of this video , BIG SALUTE for such a lovely way of teaching and helping us. Thanks sirji 🙏🙏

[@algotradeacademy]

Wonderful explanation of foreign key

[@sitrakaforler8696]

Great vidéo man !

[@kotagirigopi8106]

Amazing classes 👏👏👏

[@rajorshi1000]

Very well explained . Thanks Sumit sir.

[@sumitmittal07]

Happy that you are finding it easy to understand.

[@Amrit-Manash]

One of the best way to explain foreign key thank you so much sumit sir for this sql free course on KZbin

[@shashankseth3041]

In production it's not a good practise to drop the table always to make any changes like adding foreign key or modifying schema so let's say you already have student table and now you have added course table and you want to add the enrolled_course column in students table and enter the courses they enrolled for then you will go in this way in production environment. Two important things table on which you applying foriegn key is child table and from the table you are pullying or taking reference of this foreign key is parent table First add enrolled_course column in student table by below command 1) alter table students add column enrolled_course int not null; Now apply foreign key on this column by below command 2) alter table students add foreign key (enrolled_course) references course(course_id); Here the catch is if there is already data in course_id in course table but as of now the enrolled_course column in the student table is blank system will not accept this command and it will throw error Error Code:1452 cannot update or add a child row: a foreign key constraint fails So to overcome this error first you will disable the foreign key checks by running the below command 3) set foreign_key_checks = 0; (0 means disabled 1 means enabled if want to enable again then you can run it again by giving 1) Now after running this command run the above command 2 again it will work Now you want to modify/enter the values in multiple rows for students for enrolled_course field so to modifu values in multiple rows you'll run the below command update students set enrolled_course = (case when student_id = 1 then 3 when student_id = 2 then 1 when student_id = 3 then 1 when student_id = 4 then 3 when student_id = 5 then 2 when student_id = 6 then 3 when student_id = 7 then 6 end) where student_id in (1,2,3,4,5,6,7); which means student with id 1,4 and 6 enrolled for course 3, with id 2,3 enrolled for course 1 and with id 5 enrolled for course 2 and with id 6 enrolled for course Also when you will do desc student to see the structure of student table the enrolled_course will show as MUL KEY MUL KEY means its integer column and non indexed which means multiple records of same values are accepted in this column Now if you keep the command 3 set foreign_key_checks = 0; Like this only when you will do insert for any student with enrolled_course id out of range in course id it will still accept because this check is disable so its advisable to enable it again you can try by inserting any column in student table with course id out of range see it will accept that so enable this again. That's why its mandatory and always advisable to keep this check always ENABLE 4) set foreign_key_checks = 1; After enabling this delete the record you entered above with out of range course_id to verify this check with false course_id to maintain the reference integrity of the table. To know more about reference integrity of tables in relational DBMS refer this IBM block www.ibm.com/docs/en/informix-servers/14.10?topic=integrity-referential Now when you'll enter any record which is out of range of course_id in course table it will throw error

[@RohanNagnurwar-iv4lc]

Hi, I am new to this SQL world, just wanted to ask,with such difficulty to add the data ,why people use SQL?

[@vishnujatav6329]

Sumit sir, its really really helpful and and its amazing

[@sumitmittal07]

Happy to know that you are finding it helpful.

[@lakshyagoel1893]

Amazing explanation of relationship between two tables.Awesome content.

[@sumitmittal07]

Happy that you liked the explanation. Keep watching.

[@satviktejas8716]

WHERE ARE YOU FINDING THE NOTES WHICH SIR IS PREPARING PLZ TELL

[@ankitgarg1987u]

This was really useful

[@jodu1584]

amazing content !!!!!!!!!!!!!!!!!!!!!!!!!!!

[@sangeethapadamata2965]

Best explanation of foreign key that I ever watched.

[@rajiv3444]

Thank you Sumith sir 🙏🙏

[@anupamkumar3197]

create table students( student_id int AUTO_INCREMENT, student_fname varchar(30) NOT NULL, student_lname varchar(30) NOT NULL, student_mname varchar(30), st_email varchar(30) NOT NULL, st_phone varchar(30) NOT NULL, st_alt_phone varchar(15), enrollment_date TIMESTAMP NOT NULL, year_of_exp int NOT NULL, st_comp varchar(30), batch_date varchar(30) NOT NULL, soure_of_j varchar(30) NOT NULL, location varchar(30) NOT NULL, primary key(student_id), unique key(st_email) );

[@greatmonk]

Thank you by explaining FOREIGN KEY in a most simplest way... Awesome. I am loving the sessions and believe me , this helps me too,in my day-to-day work...

[@hemanthp2702]

Could you please share the notepad in all of your lectures, as it will be a great way to refresh the contents and practice ourselves.

[@PROTECHRAHUL]

does this playlist covers Types of Relationship in DBMS?

[@kaladharnaidusompalyam851]

Thank you Sumit 👍 . Initially have doubt on Check . U cleared it at the end

[@sumitmittal07]

Happy that your doubts are cleared.

[@ashwinisingh6251]

I am in final year and teally enjoying this SQL series

[@mathaliens651]

Up to the mark explaination of Foreign Key Great!

[@pallavinijamkar7862]

Thank you Sumit sir this was an awesome session and very well explained where the basic things got pretty clear.

[@nakulmopari4247]

Thank you sir, wonderful waiting for the 5th session

[@sumitmittal07]

this was actually the 5th session.. 6th session will be released tomorrow.

[@swastiktripathy8412]

Very nicely explained which can be easily understand by students. Thank You .

[@mandakinigiri7493]

As for the enrollment date you take the data type as a timestamp then for the batch date why we selected the data type as varchar

[@mariageorge3278]

can we not use ALTER students ADD FOREIGN KEY (selected_course) REFRENCE course (course_id); ? instead of deleting the table and recreating the whole thing? for the data part we can use UPDATE command

[@mayanktaggar456]

Can you please share the file for the dummy data you are using in this video, please?

[@smyphysics]

thanks lot for the SQL stuff ..... Rohit sharma joke is nice ...

[@VishalSharma-il5yd]

Thankq so much sir for the session

[@vangapandupraveen4352]

Please share the notes link

[@jackreacher1921]

where can i get the bulky insert statement for copy paste?

[@aadhavanmahendiran8074]

This session is super interesting. where I can get the class document. especially seed data. it's too huge. previous classes are not that much. so can you share us the seed data sumit sir.

[@VarunTaliyan]

While creating the "students" table, we have defined enrollment_date as "enrollment_date TIMESTAMP NOT NULL". However, when we describe the table in next query, we can see that Default column says "CURRENT_TIMESTAMP" and Extra column as "on update CURRENT_TIMESTAMP". How does system got to know that there should be a default value for enrollment date and it should take current timestamp on update as well ? I tried this on my local mysql installation but I am not getting this behaviour. Am I missing something? Please elaborate. Thanks.

[@boredbanda3168]

Same here for some reason the default values 'CURRENT_TIMESTAMP' is to be explicitly mentioned while defining table. Else insertion in the table is failing.

[@HarshitSingh-tg9yv]

Does the foreign key in the child table necessarily references to primary key of the parent table? Can it not be any other column of the parent table?

[@keshavsaraswati1472]

so nicely explained :)

[@sumitmittal07]

Glad it was helpful!

[@_AbhiTalks_]

Sir, can you please do a course on excel and python.

[@abhilashshetty9782]

Simple and Worth watching complete SQL series! Sir please do a playlist for Python?

[@princesinghaniya3625]

hello sir when i try to run timestamp in enrollment_date field its not work its gives me error of 1364 .please guide me in this .

[@ritumdutta2438]

Can you please add "On delete cascade" and "on delete update" concepts for foreign key

[@Mukulka12]

can foreign key be refered to unique key in another table

[@nadeemshaikh5683]

please share the how important of "underscore" sign during code..You can not use hyphen while coding(for Freshers)

[@shreyagrawal3806]

Helpful video

[@balajibalasubramaniam8349]

THANK YOU SO MUCH SIR

[@Gautamprakash17]

How to add auto increment in existing mysql table(20:11min)?

[@sushyyyyyyyy]

alter table table_name modify column_name data_type auto_increment; eg. alter table student modify column student_id int auto_increment;

[@Gautamprakash17]

@@sushyyyyyyyy ty bro

[@gautamgoswami9510]

sir when I never give any default value then it takes null. not current time stamp in command line then what should I do for use current time stamp

[@harshvrm100]

Foreign Key can be constraint the table that don't belongs to our curent database??

[@karthika1490]

Please do playlist for python. That will be helpful alot for us

[@vansharora1512]

And he been in my sa situation obviously

[@gireeshkumarsingh4029]

Great Experience so far. I have some doubts sir :- 1) SQL is not a case sensitive language yet when you asked to describe courses table by giving table name in capital it shows an error?? 2) Why have you given phone no. VARCHAR data type why not INT data type?? Thank you!!

[@parthagrawal5516]

I try to type int, No issue, I think sir has taken randomly. we can use int, then no issue.

[@mmartel02]

datatype (INT, VARCHAR, DATE, TIMESTAMP...) and functions (CREATE, ADD, DROP, ALTER, MODIFY, CHANGE, UPDATE, DELETE.....) are not case sensitive. You can refer to them either using upper or lower case; however the industry standard is to use upper case for readability. Your Table and Data ARE case sensitive. Table students, STUDENTs and Students would all be different. I hope this is clear.

[@abhisheksingh-ij1ho]

where we will get these notes

[@architkumar1265]

I'm having a doubt, can we update all the records of a particular column at a same time ( Ex: lets say, we have column, "selected course" with the default value, 1, in every record). Now, if we want to change every record's value then can we update all records at same time (with update command) as INSERT command works, (to insert multiple values)..............?

[@dhairyashiil]

This course is fire 🔥

[@jen4986]

15:07 I get this error. "ERROR 1364 (HY000): Field 'enrollment_date' doesn't have a default value"

[@jen4986]

Difference maybe because I am using command line. So I solved it the following way. enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP Thank you for your lectures.

[@GudduKumar-wy1hi]

This video is good, but one thing I observed that course fee is high. Please look into this if possible. Since Generally middle class or lower middle class family student go for normal institute otherwise who is capable go for brand.

[@aayushmaan5887]

I am in doubt that while deleting the course_id =2 ,it was throwing error because of data entered in child table .so to delete that is ,do we need to delete first record from child table and then delete the course_id 2 from couse table ? Or Is there any other way to do so ???

[@syedasherahmed949]

Field 'enrollment_date' doesn't have a default value. This error is showing although I have checked the data and how can i add in EXTRA update CURRENT_timestamp?

[@nganthoibahidam7282]

Hi Sumit Sir, unique key assigned while creating a table is not allow in MSSQL, which give an error "Table level constraint doesn't specify column list". KIndly suggest why it is happening.

[@sa2742]

Thank you

[@nareshkonukati5647]

Hi sir when I am using pgadmin4 for practice, I didn't get the output for some data

[@isharkpraveen]

Nie video... I didnt drop the table.. I just alter the table to add column

[@shubhamSharma-du4ir]

Thank you Sir

[@pawanseth70]

Thanku very much sir

[@munnalalsharma1752]

Thanks

[@TomJerry-bp9ig]

Why the foreign key should always point to a "primary key" in parent table, why not any other key?

[@swarajkumar9550]

while using timestamp in mySql, the syntax which you are explaining seems to be wrong..and thus values could also no the inserted un their, its saying enrollment date doesn't have a default value.

[@keshavkarki7775]

same with me

[@gamersassemble539]

@@keshavkarki7775 @Swaraj kumar Use this : ALTER TABLE students MODIFY enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

[@raghavrocks94]

@@gamersassemble539 woooorrkkkedddd !!!

[@mahadevduary3839]

where can i get the pdf or text file of this course

[@sumitmittal07]

Strongly advice to make notes while watching videos.

[@thenaveed5195]

Can you upload the data and commonds used in this video?

[@sumitmittal07]

Will try including the most important commands in the description if feasible.

[@learntoday7783]

29:00

[@harshrajvaghela699]

It's true or example data sci ,devops,web delopment

[@sumitmittal07]

I offer only Big Data Course :)

[@challavamsikrishna4028]

Sir I want your document can you please post the entire SQL course document

[@BeyourBest1]

Error cannot add foreign key constraint

[@karthikjesus1519]

sir can we add multiple tables in single database??

[@sumitmittal07]

Yes a single database can have multiple related tables.

[@saranshsehrawat8544]

In MySQL workbench enrollment_date TIMESTAMP NOT NULL not working. i have to used TIMESTAMP DEFAULT NOW()

[@deeptanshupandey6696]

In mine too its throws error.

[@mohitjain6168]

I think you should have added the data file (the data of students table) in the description.

[@sumitmittal07]

Oh yes.. will add

[@siddharudtevaramani1055]

@@sumitmittal07 please add it

[@abhinavkumar1069]

@@sumitmittal07 Please add that, it would be really helpful!!!

[@jagrati2406]

How to handle this scenario in real world? If we want to discontinue the course_id =2. At the same time, we want to keep the record of all students who have enrolled before but new students shouldn't be able to enroll to this course?

[@abusadatmohammadsayem1311]

Or other way around update the course table to exclude course_id=2, then we should be good to go.

[@raghavmittal7112]

I had take offline coaching and seen many lectures its been 6 month noone able to teach properly how foreign key used why used first time i understand from non tech background

[@harshrajvaghela699]

How to delete multiple values How to update Multiple values

[@sumitmittal07]

This will covered in the upcoming sessions.

[@mmartel02]

Use a WHERE clauses with multiple 'OR' command to catch all the records you would like to update or delete For example, you can delete the first two rows of your student table with the following command: DELETE FROM students WHERE student_id=1 OR student_id=2;

[@rekharanibaranwal3995]

Everything is fine but everytime dropping the table is frustrating

[@anandsingh-pr6rr]

thank you sir......one request from sir please share the data that you have used so that we can perform logical operation

[@mmartel02]

Manually typing the commands - while tedious and repetitive - is a great way to familiarize yourself with writing SQL commands. I suggest typing everything manually rather than copy pasting code.

[@amuladhanunjay7946]

Waiting for 📝 notes sir

[@deepakkumarpandia917]

Did you get the notes ?

[@amuladhanunjay7946]

@@deepakkumarpandia917 no

[@VignanMani]

sir can you please share the document

[@medhabobbili5018]

can anyone feel that the sound is too low in the videos ?

[@durgaprasadpatra892]

Time stamp is not working sir,guide please

[@therisingsinger4330]

A small suggestion to u sir , aap Etna bada table banake time bahot waste kiye ,small table se v concepts clear ho sakta thaa😅😅...our main goal is to understand foreign key ...

[@sumitmittal07]

Ok next time

[@divyanshujha3908]

Sir, can you please add a text file containing all the records, so that we can practice the commands ourselves. Thank you

[@hemanthp2702]

Let know if you have received it...?

[@divyanshujha3908]

@@hemanthp2702 haven't yet

[@hemanthp2702]

@@rajatkhanna9886 I am also awaiting this...

[@KundanKumar-tu8dj]

Great explanation..thanks sir for wonderful SQL session

[@dilawarjalil8842]

Thanks Sir