Learning MySQL - FOREIGN KEY CONSTRAINTS
Вставка
- Опубліковано 1 чер 2019
- This tutorial covers what Foreign Keys are, what Constraints are, how to create foreign keys, constraints and how to implement actions based on the constraints.
MySQL Playlist:
MySQL Foreign Key Constraint Reference:
CODE SAMPLE:
ALTER TABLE characters DROP FOREIGN KEY `fk_character_race`;
DROP TABLE IF EXISTS races;
DROP TABLE IF EXISTS characters;
CREATE TABLE races (
race_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
race_name VARCHAR(30) NOT NULL
)ENGINE=INNODB;
CREATE TABLE characters(
character_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY Key,
character_name VARCHAR(50) NOT NULL,
race_id TINYINT UNSIGNED NOT NULL,
INDEX `idx_race`(race_id),
CONSTRAINT `fk_character_race`
FOREIGN KEY (race_id)
REFERENCES races(race_id) ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=INNODB;
3 years later and its still useful, love these timeless education videos.
Thank you for putting the effort and time to make these!
Gimli to Legolas: I never thought I’d end up in the same database as an elf!
Thank you, thank you! This video helped me with several syntax errors I had. You also explained foreign key constraints in a way that finally made sense to me!
I've been using MySQL for a couple of years but never created foreign keys despite the advice of fellow developers because i dont have clear understanding about it until now thank you.
Thanks a lot. I'm having trouble in creating relationships in MySQL and this really helped me.
Explained the right way, easy to follow. Great video!
Very good video! Been trying to figure out how to do this for a long time. Specifying on update and on delete helped me out very much!
5:10 is when he writes the actual query to make the link between the tables. Thanks alot for the videos, they're very high quality!
Thank you Steve! You don't know how happy I am (new to MySQL) to find your video. This is really helpful. Very clear explanation on Foreign Key! I salute you!
Be sure to check out the rest of the MySQL playlist too
Thank you Steve, keep doing what you do!!
Whether it's for school or work, Steve's videos will always save your ass👌
Really well done lesson! You earned a subscriber!
Thank you very much! from Buenos Aires, Argentina.
Thank you so much! Great presentation and clear understanding.
This is awesome!Thank you so much
Great explanation.
Absolutely loved it ♥️
Very clear and understandable. Thank you
Very bright Explanation! Thank a Lot
thanks for the tutorial! really good
you're great sir , you made me love it though i'm not a specialist , I just have a little job that is related to mysql
Thanks! Very nice explanation!
excellent example and explanation!
Your videos are so good that one like isn't enough.
This is awesome!
Thank you.
Nice!
Great job Man... Please could you make a video based on table with blob data type ?
Please add your requests in the comments here - ua-cam.com/video/LCezax2uN3c/v-deo.html
Professor, really thank you for these tutorials.
May I ask you one thing: "when" someone may be classified as an "expert" in SQL? These tutorials, from one to ten, how much do they say about sql skills for a job?
Well there will be 40 or so videos in this course. If you really understand everything that I cover then you really should be capable of doing most development work with MySQL. I would say that this is a DBA course because I'm not covering management of the database or optimization to a great degree. So I would not expect you to be an expert but competent and capable yes.
@@SteveGriffith-Prof3ssorSt3v3 I appreciate your answer Professor; I'm studing for becoming an etichal hacker by using open source tecnologies like Kaly Linux, and since it run a PostgreSQL DB, your course is a great boost to my studies; for now i'll use my LAMP Xubuntu for training.Thanks again.
Great video, but what if I wanted to delete Men out of the Races table? Would I have to remove all instances of Men from the Characters table first?
That or set up a trigger to do the deletes for you
You mentioned what when you have a foreign key, that you should add an index to it. What is that? And could you maybe explain what the index does? (3:32)
If you don't create the index on the foreign key it will be automatically created for you.
Indexes are to improve search performance on tables - ua-cam.com/video/yWj320oEQEo/v-deo.html
Hi, first of all, many thanks for your tutos... they are super clear !
I've got a question:
At minutes 8:00, when you click on the dropdown list, you've got the "id" and the "race" value.
When I try this on my computer, i've got the "id", but, in my case I would have the "customer_name", instead i've got the "city" of my customer.
I've checked my FK twice, and it seems to be correct.
Any idea?
Thanks for your help
If you had previous foreign keys with the same name or on the same columns then it could just be phpMyAdmin using a saved cached reference value.
@@SteveGriffith-Prof3ssorSt3v3 I don't think, but I forgot to mention that I've imported the tables from an SQLite DB.
Anyway, I will try to clear the cache.
Thanks for your help💪🙏
@@KonohaNoSai2008 yeah. Without seeing exactly what was imported and set up it's hard for me to say.
09:43 Is it possible to delete rows at the character table? Probably yes because their primary key does not used in other tables?
yes. correct.
Great video thanks so much!!! I have a question. What if say Soren killed all humans. I want it instead of preventing the delete to delete all the human entries and delete the race entry?
You would want to use ON DELETE CASCADE as the trigger. to remove all entries where the race_id matched the value in the RACES table that you delete. Deleting the value in the RACES table is the starting point.
@@SteveGriffith-Prof3ssorSt3v3 Thank you! :)
Do you sleep? Great video!
I set up a couple of tables with foreign key constraints similar to your clip. I had already created the two tables, so I designated constraints using the Relation View. When I tried to insert or update the id field, it only shows the number ("2" instead of "2-Man" in your example). This makes inputting the data much more inefficient. Do you know how I may be able to fix this problem?
Sounds like you are missing the index that I created on the second table, when creating my foreign key.
@@SteveGriffith-Prof3ssorSt3v3 Thank you for your prompt comment. I thought I did but I may be wrong. I will try your example using SQL to see what I am doing differently.
@@SteveGriffith-Prof3ssorSt3v3 The result was the same with SQL. I only got the number. Perhaps it is the phpMyAdmin available from the rental server. It's version is 4.4.15.8.
@@SteveGriffith-Prof3ssorSt3v3 I tried the latest phpMyAdmin ver. 4.9.7 from MAMP and it worked the same way as your example. Thank you!!
W/o any of offensives, It's really bad idea having same name 'race_id' in 2 different tables in my opinion. It makes hard to figure out which race_id is in race table or character table.
If the field is the primary key in one table and the foreign key in another then they are the same number regardless of the table. In your queries you should always include the table name or alias. It is actually a best practice to give them the same name. That way you can tell at a glance that they are related keys.
If the fields are not related, or not the same values, then you shouldn't give them the same name.
Hi, great tutorial. Never used Foreign key in my web applications. Appreciate this tutorial, It's great but how make "characters.race_id" follow "races.race_id" while inserting new records in "races" table and "characters" table ? Do they must have the same "Autoincrement" value when start populating them ? Or/and...what if I make "insert" queries only into one of them ? Except for the reasonable motife to avoid the accidental corruption of data, I can't really see why "Foreign Key" must be used....Please convince me that I'm wrong. Thanks in advance.
Inserting into two tables is two steps, two separate queries.
The auto increment value is only used for a primary key. When you insert into two tables, do the one with the auto increment value first. Then that number gets used as the foreign key in the other table.
Constraints control what happens when a foreign key value doesn't match the primary key in the related table, or if you are deleting a record whose primary key is a foreign key in another table. Do you want to delete the related records? stop the delete happening? or go ahead and let the record have a null foreign key value.
Marking a primary key column as being a foreign key means that you are clearly explaining the relationship between two tables. Eg:
Users
user_id int (auto increment primary key)
username varchar
user_type int (foreign key here defines the connection to the UserTypes table)
UserTypes
type_id int (auto increment, primary key) this is used as a foreign key
type_name varchar
@@SteveGriffith-Prof3ssorSt3v3 Suppose that I have
1) "Products" table with prod_id Auto_increment, and...
+ - - - -+ - - - - - - +
| prod_id | name |
+ - - - -+ - - - - - - +
| 1 | prodOne |
| 2 | prodtwo |
+ - - - -+ - - - - - - +
2) "images" table with id_image in Auto_Increment
+ - - - - + - - - - + - - - - +
| id_image | filename | id_prod |
+ - - - - + - - - - +- - - - +
| 55 | one.jpg | 1 |
| 68 | two.png | 1 |
| 76 | tree.jpg | 2 |
| 76 | four.jpg | 1 |
| 76 | five.jpg | 2 |
| 76 | six.jpg | 1 |
| 76 | seven.jpg | 2 |
+ - - - - | - - - - | - - - - +
What i'm suppose to do for automatically store the "prod_id" of a new product just inserted into the "id_prod" column of images table without to recour the special native functions like mysqli_insert_id($conn) or similar stuff, but with a foreign key constraint instead ?
Does a foriegn key has to point to primary column...?
Yes. That is what makes it a foreign key.
@@SteveGriffith-Prof3ssorSt3v3 you're the best teacher ever, thanks for the answer.
what is the use of the constraints name please?
A constraint is a type of restriction that you are putting on the table. You are adding restrictions on what data is allowed to be added to the table (or updated or deleted) dev.mysql.com/doc/refman/5.7/en/constraints.html
Thanks alot for the response. I understand what the constraint is for . I just don't understand why it had to be named since the name wasnt referenced anywhere else in the query .
You will almost never reference a constraint by name. It does its work behind the scenes. It is making sure that you other queries don't do things that you don't want to have happen.
@@SteveGriffith-Prof3ssorSt3v3 Alright, i see. Thank you.
can foreign key be null?
If you allow null as a value for the column yes. But then it's not connected to the other table.
might you have deleted the links you said you would post?
What links?
Why do you need that many browsers??? 🤔🤔
As a web developer I need to be able to test features across all the platforms. Lots of browsers, different versions and I have other OS with the browsers too.
@@SteveGriffith-Prof3ssorSt3v3 👌
That example is so racist :D
fk_character_race ! IYKYK