MySQL Bugs: #8553: Constraint violation messages

Bug #8553	Constraint violation messages
Submitted:	16 Feb 2005 18:06	Modified:	12 Aug 2005 9:56
Reporter:	Ralf Wiebicke	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1.8a	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I would really like to have the name of a violated constraint in the error message. For instance if one violates a foreign-key-on-delete-restrict constraint, mysql returns:

   Cannot delete or update a parent row: a foreign key constraint fails

No hint on the constraint that has been violated. Oracle is much more helpful:

   ORA-02292: integrity constraint ("USER"."EMPLOYEE_DEPARTMENT") violated - child record found

for example. The same with the violation of a unique constraint. The error message contains the bad values inserted but not the name of the violated unique constraint.

This is especially important, if one wants to deal generically with databases.

How to repeat:
create table hallo ( id varchar(8), parentid varchar(8), constraint zick primary key(id), constraint zack foreign key (parentid) references hallo(id) ) engine=InnoDB

insert into hallo (id, parentid) values ('zopp', 'zipp')

return error message:

Cannot add or update a child row: a foreign key constraint fails

but it should somehow contain the information, that 'zack' violated

Ralf,

I assume that you know that SHOW INNODB STATUS prints a detailed explanation of the latest foreign key error.

Better foreign key error messages are in the TODO.

Regards,

Heikki

Hi!
Thank you for the fast response.

I did not know about SHOW INNODB STATUS. I tried it and I have three problems with it:
(1) The command requires super privilege, and in our application one doesnt want give the application server the super privilege.
(2) The latest foreign key violation is some kind of global error variable. With many threads accessing the database concurrently one cannot be sure, that the error in the innodb status really belongs to the error message gotten a moment before.
(3) The is no solution at all for the unique constraints.

So I'll try to workaround in the application.

Best regards,
Ralf.

I had a close look at appendix C "MySQL and the Future", but I couldn't find anything about constraint error messages. Is there any other TODO list I'm not aware of?

Ralf.

Duplicate of #3443.