Code. Models. Analysis. Decisions.
Follow @mjmacartyMySQL Foreign Keys
How to Add Foreign Keys and Relationships in MySQL
MySQL is one of the most popular Database Management Systems in the world. Based on ANSI SQL, the MySQL query language executes most typical queries using the same syntax as other major management systems such as Oracle and Microsoft SQL Server. MySQL is suitable for development projects of all sizes from one off data analysis to data warehousing implementations.
Workbench is a graphical user interface that allows a nice combination of point-click-drag functionaltiy to augment standard SQL.
Add a Foreign Key & Create Relationship with MYSQL Workbench
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 two 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. Here we will ALTER the tables using the MODIFY keyword on the columns of interest to add both primary and foreign key.