Creating a Foreign Key in MySQL

Author: Steven Neiland

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

I recently encountered a MySQL database with no keys defined. When I asked the original developer the response was that "since MySQL could not create foreign keys it was pointless to define any keys".

There are so many things wrong with the above statement that I could write a book but I am going to just deal with the foreign keys component of it today.

Foreign Keys Have Been In MySQL Since November 2001

It seems to be a common misconception (among some developers) that MySQL does not support foreign keys. In fact foreign keys have been present in MySQL since MySQL 3.23.44 (we are currently up to version 5.6.x). In addition creating foreign keys in MySQL is just as simple as any other relational database, and can be done at table creation or at a later time using the alter table command.

Create Foreign Key During Table Creation

In order to define foreign keys we need to ensure that a table uses the InnoDB engine. This is done by specifying "ENGINE=InnoDB" as part of the create table command.

Then when we want to create any foreign key(s) during table creating we use the following syntax.

Foreign Key ([fk_col_name]) references [ref_table_name]([ref_table_col])

So for example if we want to create a table of customer orders where the "Customer_SID" references the "SID" column from the "Customers" table we would use something like this.

Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID)

Convert Existing Tables To InnoDB

If we are working with an existing table we can converted it to InnoDB type very simply by using the following command.

ALTER TABLE [tablename] TYPE=InnoDB 

Create a Foreign Key on an Existing Table

Now to create a foreign key on an existing table we can do the following.


So there it is, foreign keys in MySQL made simple. Please folks, I really don't want to ever hear this excuse for not defining foreign keys ever again.

Related Blog Postings

Reader Comments

Fifah's Gravatar
Wednesday, January 15, 2014 at 10:55:17 PM Coordinated Universal Time

Thank you.. You've made my day :)

Ben's Gravatar
Wednesday, May 18, 2016 at 2:30:23 AM Coordinated Universal Time

Very helpful information.
i was creating sometables at command prompt and needed clarifications on this.
it really helped me. thanks for sharing

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing