How Add a Foreign Key Constraint (Make Relationships) in MySQL WorkBench

‪@MattMacarty‬
#mysql #workbench #foreignkey
✅ Please SUBSCRIBE:
ua-cam.com/users/subscription_...
Add and work with foreign key constraints in MySQL
✅ Pure SQL Tutorial starts 7:18
✅ How to Create Databases:
• How to Create a Databa...
✅ How to Create EER Diagrams:
• How to Make and Use an...
✅ Link to GitHub:
github.com/mjmacarty/mysql
In this video I am going to demonstrate add a foreign key contraint in MySQL and create relationships using the workbench. See the link to download the two tables used from GitHub so you can follow along. The tables customers and orders, so we can imagine the relationships there where one customer places many orders (hopefully).
We start off by examining and ERD of the unrelated tables. We can use the EER diagram to manually add the one-to-many relationship button here and draw the relationship in between the tables, however for this to take effect you would need to forward engineer the diagram into a new database.
There are a number of different ways we add foreign key constraints, and the video covers the methods to do this in the workbench. First I'm going to use the GUI method, and then we will see how to do the same thing with pure SQL. The requirements are the same regardless of which method you use: you need to relate a primary key from one table to a column in a second table of the same data type and size. A couple of things to keep in mind, 1. when you import data MySQL tends to make it as generic as possible. For example any string data will be imported as text datatype; 2. MySQL does not allow text data to be defined as a primary key. MySQL allows CHAR data and INT data to be defined as primary keys. For speed, INT data is preferred, but we will be using CHAR data in this video.
Some people prefer the GUI interaction since you can simply point click and drag. In reality, though the Workbench generates all the required SQL, which you have "apply". To me it's not easier to use the GUI interface, and I prefer the PURE SQL method. In addition, since we are dealing with SQL there's always or usually more than one way of doing things. I am going to ALTER the tables using the MODIFY keyword on the columns of interest to add both primary and foreign keys.

[@shinakritphanutaiwat6717]

Thanks so much, this helped especially with making relationships through the code and demonstrating how to turn it into an ERD.

[@MattMacarty]

Glad it helped

[@SidAhmedHachoud]

Thank you so much, this was literally life-saving!

[@MattMacarty]

Glad it helped!

[@dellyemaya9184]

Thanks again. You are the best!

[@MattMacarty]

Glad it helped

[@koraxtu5989]

Thanks for this vid, it was very helpful!

[@MattMacarty]

Glad it helped

[@Adekorede]

Thank you so much Matt

[@MattMacarty]

My pleasure. Glad it helped.

[@HarryScutt]

You rescued my uni project thank you so much

[@MattMacarty]

Glad it helped

[@faizanwar_]

Nailed it ...that's the way to do it👍👍

[@MattMacarty]

Glad it helped

[@user-bk6ck6ju7h]

Perfect explanation, well concise

[@MattMacarty]

Thanks. Glad it helped

[@ironrival9651]

Wow this is a very good tutorial. Thanks a lot.😃

[@MattMacarty]

Thanks. Glad it helped

[@michaelowens2747]

Really helpful

[@MattMacarty]

Glad it helped

[@kamalkushwah2659]

Thank you so much for this type of detailed video you save me...

[@MattMacarty]

Glad it helped

[@TiiLiiXx]

Thank you, it helped me

[@MattMacarty]

Glad it helped

[@Adrianacmp]

You literally saved me! I've been watching videos for about 3hs and had not found any help! Thanks to you I could almost complete my first data base. The only thing left is to set some 'domains' (like a list of options that must be available when introducing new data in a field. is there any chance you could make a video about it? I'd really appreciate it!
Anyways, thanks a lot for this video. You really know how to explain yourself and make it look so easy!

[@MattMacarty]

Let me see what I can do, but there is a datatype in MySQL called ENUM, that does what you want. I talk about it briefly in this video: ua-cam.com/video/68XMlO9fciA/v-deo.html starting at 14:30

[@Adrianacmp]

@@MattMacarty Thanks a lot man! I'll check it

[@miguelangelayalaleiva4939]

Me fue de gran ayuda. tank you

[@MattMacarty]

Gad it helped

[@appajik858]

thank u so much

[@MattMacarty]

Glad it helped

[@pranavarora724]

thanks man

[@MattMacarty]

Glad it helped

[@frog706]

Brilliant video, first one that was actually useful.

[@MattMacarty]

THANKS. Glad it helped.

[@neux64]

"you can't make primary/foreign keys using text attibutes"
This was it, I was wondering why the latest version of MySQL/phpmyadmin was telling me I couldn't do it. I thought it was a bug since both of my datatypes were the same, but they were both TEXT type, so that's why. Thanks!

[@MattMacarty]

Glad it helped. MySQL won't let you use varchar either.

[@montrafons]

Thanks for the explanation, it is very easy to understand.
I have a question, when the SQL query has been done and the relationship between the tables has been formed in the form of an EER Diagram, it's the relationship "One to Many", is it automatic from the workbench or can we change it for this relationship? Then is there any impact on the relationship between tables in Schemas, if we change from "One to Many "to" One to One " in the EER Diagrams section?
Thanks

[@MattMacarty]

The database engine tries to determine the relationship when you add the foreign key constraint. You can edit and reimplement, but typically we don't see 1:1 relationships that often. They are usually between two primary keys.

[@montrafons]

@@MattMacarty Okay buddy, in what cases do we need 2 primary keys? Suppose that one table already has primary keys, then add primary keys from another table

[@miguelangelayalaleiva4939]

Gracias

[@MattMacarty]

De nada

[@ate-trainingplcprogramming4837]

Vielen Dank . IT means in German thanks 10000x

[@MattMacarty]

Glad it helped

[@sweatermanny94]

Very useful but what about creating constraints for specific fields? For example: the field qauntity in the cart table must be more or equal to zero. Any idea on how to create those in workbench?

[@MattMacarty]

Yes you can put a UNSIGNED constraint on numerical data so : (Quantity TINYINT DEFAULT 1 UNSIGNED), this would limit input to 0 -127 and set a default value of 1

[@anthonyfallon6070]

I don’t know what this means, can you maybe give an example how this would be used?

[@Justin-mx5ye]

Really insightful video. Thanks for the upload. Any chance you can upload something on complex monte carlo simulations in Python? I'd like to learn more about portfolio management from this perspective.

[@MattMacarty]

I have this one: ua-cam.com/video/A0J0VAHzIxc/v-deo.html What were you thinking?

[@Justin-mx5ye]

@@MattMacarty I thought this was a helpful video. I am interested in learning more about fundamental research of the stock market using python. For example, I have used monte carlo simulations to simulate varying weights of equities to maximize a sharpe ratio. Any general portfolio management/python videos are always fascinating to me. I know you're probably busy, so thank you so much in advance.

[@user-fb7hy7gn4t]

can you please describe if there are contents in the table which you have created will the contents also move to another table after foreign constrain is done and what is the use of this, kindly help

[@MattMacarty]

No the content will not move from one table to another. You are creating a relationship with the foreign key so that you can't add or delete data from tables improperly.

[@user-fb7hy7gn4t]

@@MattMacarty thankyou for the response may I know if I need to copy the data from one table to another table is it possible

[@iAmGratchet_]

Is it possible to add mulitple values in a column?

[@MattMacarty]

Yes but this violates first normal form. What did you have in mind?

[@45_dzikrimufidtsaqif33]

How to do if the table connection we want is in a different schema sir?

[@MattMacarty]

probably the easiest thing to do is copy the table into your schema

[@Huszan]

My referenced column contains only 'Specify Column...'

[@MattMacarty]

You need to enter the primary for the table that is related

[@danilorosado8868]

@@MattMacarty I still don't get it

[@jakeevans598]

I have error: Duplicate foreign key constraint name

[@MattMacarty]

sounds like you are trying to add the same foreign key constraint twice

[@BrianGarside]

Helpful, but take too long to make a point like when creating a foreign key.

[@MattMacarty]

OK

[@EricNsi]

You sound like the dude from ua-cam.com/channels/mrLCXSDScliR7q8AxxjvXg.html

[@MattMacarty]

Yeah sort of I guess

[@MattMacarty]

But gosh I don't push propaganda do I?

[@EricNsi]

@@MattMacarty SQL propaganda I guess