Last update: 2019-12-16

Creating a foreign key in MySQL


The idea behind foreign key in a database is that one table relates to another table. The common example is that you may have a table containing users which has these fields:

  • id: The ID of the user
  • name: The full name of the user
  • email: The email address of the user

Then you may have a table with purchases, which links to the user table:

  • id: The ID of the purchase
  • date: The date of the purchase
  • item: The item purchased
  • price: The price of the item
  • user_id: The id of the user

Technically, you don't have to link both tables. You could fill the user_id field with an idea from the users' table and just assume it's valid. But adding it as a foreign key just makes sure that the database will enforce that the id actually exists in the other table.

This is how you can create the two tables:

CREATE TABLE users (id VARCHAR(20) UNIQUE NOT NULL PRIMARY KEY, name VARCHAR(200), email VARCHAR(50));
CREATE TABLE purchases (id VARCHAR(20), date VARCHAR(20), item VARCHAR(50), price BIGINT, user_id VARCHAR(20), CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES users(id));

From there, you can use joins to get information about both tables. For example, you could get a list of purchased items and prices along with user names, for items that cost more than $10:

SELECT u.name,p.item,p.price FROM purchases p INNER JOIN users u ON u.id = p.user_id WHERE p.price > 10;

Looking to expand into the cloud? See what a certified AWS administrator can do for you.


 
© 2007-2020 Patrick Lambert - All resources on this site are provided under the MIT License - You can contact me at: contact@dendory.net