• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact

CodeSamplez.com

Programming, Web development, Cloud Technologies

You are here: Home / Database / Primary/Foreign Key Constraints On MySQL

Primary/Foreign Key Constraints On MySQL

October 24, 2010 by Rana Ahsan 1 Comment

Mysql Phpmyadmin

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’.
MySQL/PHPMyadmin Relation View Option
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 🙂

Share If Liked

  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)

You may also like

Filed Under: Database, Development Tagged With: mysql, phpmyadmin

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Reader Interactions

Comments

  1. thecreativedev says

    November 2, 2015 at 12:33 am

    Hello,

    Thanks for the post. very informative and relevant

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Follow Us

  • Twitter
  • Facebook

Top Posts & Pages

  • How To Work With JSON In Node.js / JavaScript
    How To Work With JSON In Node.js / JavaScript
  • PHP HTML5 Video Streaming Tutorial
    PHP HTML5 Video Streaming Tutorial
  • How To Work With C# Serial Port Communication
    How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
    Facebook C# API Tutorials
  • Using Supervisord Web Interface And Plugin
    Using Supervisord Web Interface And Plugin
  • Utilizing Config File In C#.NET Application
    Utilizing Config File In C#.NET Application
  • LinQ Query With Like Operator
    LinQ Query With Like Operator
  • Getting Started With UDP Programming in Java
    Getting Started With UDP Programming in Java
  • Generate HTTP Requests using c#
    Generate HTTP Requests using c#
  • How To Use Hotkeys/Keyboard Events In WPF Application Using C#
    How To Use Hotkeys/Keyboard Events In WPF Application Using C#

Recent Posts

  • Building Auth With JWT – Part 2
  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read

Tags

.net angularjs apache api audio auth authenticatin aws c# cloud server codeigniter deployment docker doctrine facebook git github golang htaccess html5 http javascript jwt linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty socket.io tfs tips unit-test utility web application wordpress wpf

Footer

Archives

Follow Us

  • Twitter
  • Facebook

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Copyright © 2023