Primary/Foreign Key Constraints On MySQL

We, software developers, sometimes take the headache to solve such problems which actually has very easy/integrated solutions, we just don’t know that it does exist and don’t know how to use the solution. In this tutorial, I will try to discuss such an issue on database management. For small/medium scale applications, developers usually take care of the database part also, besides the coding. But, not necessarily they are expert in database management. As a result, being geek or with average knowledge in database management, developers often miss some best ways there and try to implement those through our programming skills most of the required tasks, results in a longer turn around time/ focus out from other important aspects of the applications and sometimes even result in buggy application as well. Lack of knowledge for implement foreign key constraints in MySQL database and utilize it properly is such one of the most common issues in database part.

What May Happen If We Don’t Use Foreign Key Constraints:

if we don’t make the relationship via database, then we will need to handle that via our coding. That means handle when to delete child tables row upon deletion of parent row. But these manual management can results in a mistake anytime and as a result an inconsistent db with many garbage data and more worse, can result in invalid real time data if you don’t use auto increment for assign id and using most max id available. In such case, if parent row is deleted, but child row didn’t and same id is being assigned to another user later time, then all data of that id on the child row will be assigned to the new user even if this don’t deserve that data. Also, even if we use auto increment all the time, still these non-deleted data are being incremented day by day and resulting in a slow performing db.

Additionally, there are several database abstraction library/ORM which requires the relation set up properly for proper settings, which in return reduce a lot of pain for writing complex query and provide easy to understand class/object API for accelerated application development. So, it’s best to have this learned.

How To Implement Foreign Key In MySQL?

By default settings, MySQL database doesn’t provides using this feature. This is because of the database engine, which is ‘MyISAM’ by default, which performs better in normal cases. But to get our required features, we will have to change the db engine of the table to ‘Innodb’.

To do so, go to ‘Operations’ tab on your specific table that you want to change db engine of. Then, on ‘Table options’ section, change the ‘Storage Engine’ drop down list from default ‘MyISAM’ to ‘InnoDB’ . Remember, you will have to change database engine for both parent table and child table.

After accomplish this, we are ready do the other part, linking one tables column as foreign key to the other column of the primary table. Go to your child table, select ‘structure’ tab on PHPMyAdmin. There, below the table structure, you will see an option named ‘Relation view’.

Click there to go to set/change your primary key/foreign key relation. You will see options to set foreign key to the next of the columns of the child table where you have set any index to(like primary key,unique key, index etc). Then select the correct foreign key from the drop down list. Additionally, you can set action for update and/or delete action for parent table. The actions are ‘CASCADE’,’SET NULL’,’NO ACTION’ and ‘RESTRICT’. Select which one suits better for your implementation.For example, on delete a row of parent table, you will might want to delete related row on child table also, then you can set ‘ON DELETE’ option to ‘CASCADE’.

Although I have shown you the visual way to set this up, that doesn’t mean that, you can’t do this via query. Of course you can.
To change the db engine, you can use the following query:

ALTER TABLE table_name 
ENGINE = InnoDB

To set the foreign key constraints, use this query:

ALTER TABLE table_2
ADD FOREIGN KEY (column_name)
REFERENCES table_1(column_name)

Now you are fully set and ahead with the opportunity that mysql/phpmyadmin gives in your development task.
Also, don’t forget to use best practices of database management all the times you are working. Happy coding 🙂