SQL session 5 | Foreign Key Constraint - Complete Demonstration | Trendytech
Вставка
- Опубліковано 7 вер 2024
- 𝐖𝐚𝐧𝐭 𝐭𝐨 𝐌𝐚𝐬𝐭𝐞𝐫 𝐒𝐐𝐋? 𝐋𝐞𝐚𝐫𝐧 𝐒𝐐𝐋 𝐭𝐡𝐞 𝐫𝐢𝐠𝐡𝐭 𝐰𝐚𝐲 𝐭𝐡𝐫𝐨𝐮𝐠𝐡 𝐭𝐡𝐞 𝐦𝐨𝐬𝐭 𝐬𝐨𝐮𝐠𝐡𝐭 𝐚𝐟𝐭𝐞𝐫 𝐜𝐨𝐮𝐫𝐬𝐞 - 𝐒𝐐𝐋 𝐂𝐡𝐚𝐦𝐩𝐢𝐨𝐧𝐬 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫!
"𝐀 8 𝐰𝐞𝐞𝐤 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐝𝐞𝐬𝐢𝐠𝐧𝐞𝐝 𝐭𝐨 𝐡𝐞𝐥𝐩 𝐲𝐨𝐮 𝐜𝐫𝐚𝐜𝐤 𝐭𝐡𝐞 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰𝐬 𝐨𝐟 𝐭𝐨𝐩 𝐩𝐫𝐨𝐝𝐮𝐜𝐭 𝐛𝐚𝐬𝐞𝐝 𝐜𝐨𝐦𝐩𝐚𝐧𝐢𝐞𝐬 𝐛𝐲 𝐝𝐞𝐯𝐞𝐥𝐨𝐩𝐢𝐧𝐠 𝐚 𝐭𝐡𝐨𝐮𝐠𝐡𝐭 𝐩𝐫𝐨𝐜𝐞𝐬𝐬 𝐚𝐧𝐝 𝐚𝐧 𝐚𝐩𝐩𝐫𝐨𝐚𝐜𝐡 𝐭𝐨 𝐬𝐨𝐥𝐯𝐞 𝐚𝐧 𝐮𝐧𝐬𝐞𝐞𝐧 𝐏𝐫𝐨𝐛𝐥𝐞𝐦."
𝐇𝐞𝐫𝐞 𝐢𝐬 𝐡𝐨𝐰 𝐲𝐨𝐮 𝐜𝐚𝐧 𝐫𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐟𝐨𝐫 𝐭𝐡𝐞 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 -
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLINR
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐨𝐮𝐭𝐬𝐢𝐝𝐞 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLUSD
𝐖𝐚𝐧𝐭 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐁𝐢𝐠 𝐃𝐚𝐭𝐚 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫?
𝐜𝐡𝐞𝐜𝐤𝐨𝐮𝐭 𝐭𝐡𝐞 𝐛𝐢𝐠 𝐝𝐚𝐭𝐚 𝐜𝐨𝐮𝐫𝐬𝐞 𝐝𝐞𝐭𝐚𝐢𝐥𝐬
𝐖𝐞𝐛𝐬𝐢𝐭𝐞 : trendytech.in/...
I have trained over 20,000+ professionals in the field of Data Engineering in the last 5 years.
In this session we have talked about following things
TABLE OF CONTENT
0:53 Create student table
11:30 Insert seed data in a student table
17:00 Create courses table
19:10 Insert seed data in a courses table
22:00 Create relationship among students and courses table
26:50 Foreign Key
34:20 Delete from courses table, If dependent record exists it will give error.
36:15 Theory definition of Foreign Key
41:00 Check constraints
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
🔅Sumit LinkedIn - / bigdatabysumit
🔅Sumit Instagram - / bigdatabysumit
#SQL #RDBMS #database #bigdata #dataengineering
Checkout the Big Data course details here: trendytech.in/?referrer=youtube_sql6
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.
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!
First of all thanks so much for ur comment. It really helps to know different perspective! 👍👍💖💖
@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!
In Session 4 of this course, we have learned abt ADD, DROP and MODIFY but then what is CHANGE, it didnt come till now!! ??
Use AFTER {column_name} after your ADD COLUMN command
Modify and change have the same function except change allows you to rename the column whereas modify does not
this is called real teaching. Other big tech education giants are just reading from the book and paste it on slides .
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.
This session will say how you different from other trainers..really nice explaination
Thank you and hope you would like the upcoming videos as well :)
One of the best explanation of Foreign Key. Cleared the vision of FK.
Really ................ Awesome feeling at end of this video , BIG SALUTE for such a lovely way of teaching and helping us. Thanks sirji 🙏🙏
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
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..
Very useful session however 7 seven years i came to know the actual things on foreign key. Thanks a lot Sumit Sir.
Best explanation of foreign key available on the internet!
One of the best way to explain foreign key thank you so much sumit sir for this sql free course on UA-cam
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
Hi, I am new to this SQL world, just wanted to ask,with such difficulty to add the data ,why people use SQL?
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...
Worth it to Watch. Great Explanation by Sumit Sir.
Great explanation..thanks sir for wonderful SQL session
Amazing explanation of relationship between two tables.Awesome content.
Happy that you liked the explanation. Keep watching.
WHERE ARE YOU FINDING THE NOTES WHICH SIR IS PREPARING PLZ TELL
Thank you Sumit 👍 . Initially have doubt on Check . U cleared it at the end
Happy that your doubts are cleared.
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)
);
Sumit sir, its really really helpful and and its amazing
Happy to know that you are finding it helpful.
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.
Wonderful explanation of foreign key
does this playlist covers Types of Relationship in DBMS?
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
Can you please share the file for the dummy data you are using in this video, please?
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.
Best explanation of foreign key that I ever watched.
Very nicely explained which can be easily understand by students. Thank You .
Thank you sir, wonderful waiting for the 5th session
this was actually the 5th session.. 6th session will be released tomorrow.
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
Up to the mark explaination of Foreign Key Great!
very good explanation of foreign key and it's role
Thank you
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 ???
Sir, can you please do a course on excel and python.
Simple and Worth watching complete SQL series!
Sir please do a playlist for Python?
I am in final year and teally enjoying this SQL series
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.
please share the how important of "underscore" sign during code..You can not use hyphen while coding(for Freshers)
Thank you Sumit sir this was an awesome session and very well explained where the basic things got pretty clear.
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
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 .
Amazing classes 👏👏👏
Nie video... I didnt drop the table.. I just alter the table to add column
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!!
I try to type int, No issue, I think sir has taken randomly. we can use int, then no issue.
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.
amazing content !!!!!!!!!!!!!!!!!!!!!!!!!!!
Thank you Sumith sir 🙏🙏
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?
THANK YOU SO MUCH SIR
Can you please add "On delete cascade" and "on delete update" concepts for foreign key
thanks lot for the SQL stuff ..... Rohit sharma joke is nice ...
And he been in my sa situation obviously
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)..............?
Thanks
Thankq so much sir for the session
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.
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.
This was really useful
Please do playlist for python. That will be helpful alot for us
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.
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?
Helpful video
Great vidéo man !
This course is fire 🔥
Thanks Sir
so nicely explained :)
Glad it was helpful!
Why the foreign key should always point to a "primary key" in parent table, why not any other key?
Thanku very much sir
where can i get the bulky insert statement for copy paste?
Foreign Key can be constraint the table that don't belongs to our curent database??
Hi sir when I am using pgadmin4 for practice, I didn't get the output for some data
can foreign key be refered to unique key in another table
15:07 I get this error. "ERROR 1364 (HY000): Field 'enrollment_date' doesn't have a default value"
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.
In MySQL workbench
enrollment_date TIMESTAMP NOT NULL
not working. i have to used TIMESTAMP DEFAULT NOW()
In mine too its throws error.
thank you sir......one request from sir please share the data that you have used so that we can perform logical operation
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.
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
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 ...
Ok next time
Everything is fine but everytime dropping the table is frustrating
Please share the notes link
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.
same with me
@@keshavkarki7775 @Swaraj kumar Use this : ALTER TABLE students MODIFY enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
@@gamersassemble539 woooorrkkkedddd !!!
How to add auto increment in existing mysql table(20:11min)?
alter table table_name modify column_name data_type auto_increment;
eg. alter table student modify column student_id int auto_increment;
@@sushyyyyyyyy ty bro
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?
Or other way around update the course table to exclude course_id=2, then we should be good to go.
where we will get these notes
I think you should have added the data file (the data of students table) in the description.
Oh yes.. will add
@@sumitmittal07 please add it
@@sumitmittal07 Please add that, it would be really helpful!!!
Can you upload the data and commonds used in this video?
Will try including the most important commands in the description if feasible.
Error cannot add foreign key constraint
It's true or example data sci ,devops,web delopment
I offer only Big Data Course :)
How to delete multiple values
How to update Multiple values
This will covered in the upcoming sessions.
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;
29:00
sir can we add multiple tables in single database??
Yes a single database can have multiple related tables.
sir can you please share the document
can anyone feel that the sound is too low in the videos ?
where can i get the pdf or text file of this course
Strongly advice to make notes while watching videos.
Waiting for 📝 notes sir
Did you get the notes ?
@@deepakkumarpandia917 no
Sir, can you please add a text file containing all the records, so that we can practice the commands ourselves. Thank you
Let know if you have received it...?
@@hemanthp2702 haven't yet
@@hemanthp2702 If you have a txt file for all the commands, can you please provide?
@@rajatkhanna9886 I am also awaiting this...
Very well explained . Thanks Sumit sir.
Happy that you are finding it easy to understand.
Thank you Sir