Primary/Foreign Key Constraints On MySQL

We, software developers, sometimes take the headache to solve such problems, which have 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 in database management. For small/medium-scale applications, developers usually take care of the database part, as well as the coding. But, they are not necessarily experts 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 in most of the required tasks, resulting in a longer turnaround time/ focus out from other important aspects of the applications and sometimes even result in buggy application as well. Lack of knowledge for implementing foreign key constraints in MySQL database and utilizing it properly is one of the most common issues in the database part.

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

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

Additionally, several database abstraction libraries/ORM require the relation to be set up properly for proper settings, which in return reduces the pain of writing complex queries and provides an 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 provide using this feature. This is because the database engine, which is ‘MyISAM’ by default, performs better in normal cases. But to get the features we require, we will have to change the table’s DB engine to ‘InnoDB.’

To do so, go to the ‘Operations’ tab in the table where you want to turn the DB engine off. Then, on the ‘Table options’ section, change the ‘Storage Engine’ drop-down list from the default ‘MyISAM’ to ‘InnoDB.’ Remember, you will have to change the database engine for both the parent table and the child table.

After accomplishing this, we are ready to do the other part: linking one table’s column as a foreign key to the other column of the primary table. Go to your child table and select the ‘structure’ tab on PHPMyAdmin. Below the table structure, you will see an option named ‘Relation view’.

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

Although I have shown you the visual way to set this up, that doesn’t mean 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 you for your development task.
Also, don’t forget to use the best practices of database management all the time you are working. Happy coding 🙂