Creating a Foreign Key in MySQL

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.