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 (
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.
Wednesday, January 15, 2014 at 10:55:17 PM Coordinated Universal Time
Thank you.. You've made my day :)
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