Author: Steven Neiland
Published:

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.

CREATE TABLE ORDERS (
Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID)
) ENGINE=InnoDB ;

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.

ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID); 

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

What Do You Think?

Reader Comments

Fifah's Gravatar
Fifah
Wednesday, January 15, 2014 at 10:55:17 PM EST

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

Ben's Gravatar
Ben
Wednesday, May 18, 2016 at 2:30:23 AM EDT

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

Post a Comment

Comment Etiquette:

  • 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 or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub