Tag Archives: mysql foreign key

Working with mysql foreign keys.

5 Feb , 2010,
Administrator
, ,
No Comments

We have seen that most of the time people write many lines of code. Might be the reason behind is that they either don’t know the capabilities of the each and every modules. Out of that different module one module is mysql.

As most of the websites having some kind of relational database. where their is one master table. and their keys are used in other tables. But if the programmer don’t know about the foreign keys then they write the code at the time of deleting the record e.g

Lets imagine that We have 2 tables

  1. Books
  2. Purchasers

people write following syntax.

create table books ( id smallint primary key auto_increment , name varchar(100));
create table purchasers (id smallint primary key auto_increment , bookid smallint , pur_name varchar(100));

While creating this, many programmer forgot major things. as they are creating these tables with storage engine as myISAM. but they forgot all transactional details need to be saved in InnoDB. and current released version’s of mysql support foreign key for only innodb. and Now php is being concern they write code for 2 sql statement for deleting books and their purchasers. this is really bullship.

Now We have seen that when we got the work for existing websites. we seen similar things in many cases( almost all the cases). this really increases lots of efforts in programming as well as maintaining the codes. For owner of websites , they don’t bother(rather they don’t know) what other/older programmers written on it. this increases our efforts even more. And now our database designer taking meetings with programmer and explaining the things was older database. so programmer have to find excessive work written on the code. and replace with just simple modification made by our DB designer.

First Important thing needed is to first convert required tables into innodb

alter table books engine = 'innodb'; 
alter table purchasers engine = 'innodb' ;

Then Adding of foreign keys.

alter table purchasers add foreign key (bookid) references books(id) on delete cascade on update cascade ;

Doing This will add foreign key so that when we delete any book it will delete all their respective references given on purchasers table. which can be achieved with only single sql execute in php see the following line.

delete from books where id = _NUMBER_

some time we have seen that we have to drop foreign keys. and mysql manual says that use the following syntax.

alter table tableName drop foreign key keyName ;

But now the problem is that how to get that key name. Then you need to use following way.

show create table tableName;

Doing this you will get name of foreign key. then delete the foreign key and index key. Please note that when you want to add foreign key to any table try to make foreign key symbol name by your own rather than automated by mysql. This will easier for you manage and maintaining the all the keys. every foreign key comes with 2 kays one foreign key and one index key. if you just delete only foreign key then it will not be good way. as you are keeping garbage of index kay.