Hi,

Recently we keep get complaint that implement foreign key cause a lot of problem on implementation on customer side due to keep hit to constraint error.

May i know what is the pros and cons for implement foreign key on database?
if we want to implement foreign key, how we should handle those field that which have empty value, cause some of our field is allow to be blank.

Thanks in advance

Foreign key constraints are by no means mandatory and can be implemented programmatically in your application. In other words prior to inserting the contents or updating you perform all the checks needed. If you use stored procedures local on the database then performance will be optimum for performing these checks. If you don't use stored procedures then each statement executed needs to be sent to the MySQL server, parsed and executed. This can be slow, especially if you have large numbers of records to process.

It also means that you need to use either the stored procedure or your application logic to add records making sure you have integrity.

If on the other hand you use foreign keys constraints then everything remains local in the database (performance is optimal) and a simple INSERT statement needs to be executed.

But keep in mind what foreign key constraints are there for. They are there to keep the integrity of the data in the database consistent for your application to run. Imagine you had two tables, an employee table and an accounting table. The accounting table keeps track of all employee expenses. Someone starts adding entries in this table but against and employee that does not exist in the employee table. Foreign key constraints would avoid this.