How to handle MySQL CONSTRAINT / error number

[eluser]Unknown[/eluser]
Hello CodeIgniter community,

I have an problem handling MySQL CONSTRAINT error.

For obvious reason, I can not delete record in MySQL table because another records in other table reference it.

A Database Error Occurred
Error Number: 1451

Cannot delete or update a parent row: a foreign key constraint fails (`treg`.`participant`, CONSTRAINT `fk_participant_seminar` FOREIGN KEY (`seminar_seminar_id`) REFERENCES `seminar` (`seminar_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

DELETE FROM `seminar` WHERE `seminar_id` = '31'

I need to know when this error is occurred to display appropriate error text to user.
Also I would like to have MySQL error number from last query.

How to handle this issue?

[eluser]sooner[/eluser]
check the php documentation for the mysql functions.

You can use mysql_errno() to find the numerical value of the error message and mysql_error() returns the text of the error message.

if you are using mysqli then mysqli->error and mysqli->errno

I hope it helps you.

[eluser]techgnome[/eluser]
How about using some offensive programming rather than some defensive programming? CHECK the child table FIRST... THEN delete. I you know this can happen, PLAN for it. Deal with it. Go on the offensive. Be proactive. Error handling should be for the "Ooops, I didn't count on that." situations.

Or, if your database supports it, create a foreign key relationship with a CASCADE DELETE so that when you delete the Parent, the Child records will go with it. (if that's appropriate).

-tg

[eluser]Unknown[/eluser]
RE: techgnome

You are right.

I have re-organise the code and use db transactions to fitful my needs.

Anyway, I need sometimes to handle MySQL error (for example, handling duplicated username insert) and those two function do the job:

Code:

$errtxt = $this->db->_error_message();
$errno = $this->db->_error_number();

Thank You all for support.