MySQL Bugs: #3548: CONSTRAINT NAME NOT SHOWN IN ERROR MESSAGE

Bug #3548	CONSTRAINT NAME NOT SHOWN IN ERROR MESSAGE
Submitted:	23 Apr 2004 1:45	Modified:	24 Jan 2007 13:02
Reporter:	Sanjay Patil	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	mysql-5.2.0-falcon-alpha-win32	OS:	Windows (Win 2000)
Assigned to:		CPU Architecture:	Any
Tags:	Primary Constraint name is not displayed.

Description:
Create a table with unique index on varcahr column.Try to insert the same data 
twice in the table. In the second attempt a error will be shown as the unique index is violated. e.x Error Msg
 
message from server: "Duplicate entry '100-Ford' for key 2"

   But it doesnt show the name of Unique Index,same things applys to violation of Primary key and Foregin Key constraints.

   THE VIOLATED CONSTRAINT NAMES( if any ) NEEDS TO BE SHOWN IN THE ERROR MESSAGE INSTEAD OF NUMBERING THE KEYS 1,2 AND SO ON.

How to repeat:
CREATE TABLE CAR( MODEL NUMERIC(3) NOT NULL, MAKE VARCHAR(10),UNIQUE INDEX CAR_IDX  (MAKE));

INSERT INTO CAR VALUES( 100, 'FORD'); <-- 1 Insert 

INSERT INTO CAR VALUES( 100, 'FORD'); <-- 2 Insert

   ERROR 1062: Duplicate entry 'FORD' for key 1

Here the sql specific data in this case 'FORD' needs to go away and instead of numbering the key by 1,2.. the CONSTRAINT NAME IN THIS CASE CAR_IDX HAS TO BE SHOWN.

It will be very useful if the contsraint name is displyed in the error message. F.ex If a table has multiple foreign keys and while insert if one of the foreign key fails, then currently the error messgae only says.. "Cannot add or update a child row: a foreign key constraint fails". But it does not display the name of the constraint which is quite important information while fixing the voilation. Otherwise one has to check all the referenced tables which are referred.

Thanks

Sameer

Please let me know if this feature would be made available in the next release or not.

Thank you for a reasonable feature request. Other RDBMSes do report violated constraint name, so, I hope, MySQL will include this feature some day.

In the meantime you may try to use the SHOW INDEX to find the real constraint violated:

mysql> create table tcc1 (c1 int unique, c2 int unique);
Query OK, 0 rows affected (0.27 sec)

mysql> insert into tcc1 values(1,1);
Query OK, 1 row affected (0.09 sec)

mysql> insert into tcc1 values(2,1);
ERROR 1062 (23000): Duplicate entry '1' for key 2
mysql> show index from tcc1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tcc1  |          0 | c1       |            1 | c1          | A         |     1 |     NULL | NULL   | YES  | BTREE      |         |
| tcc1  |          0 | c2       |            1 | c2          | A         |     1 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.02 sec)

mysql> show create table tcc1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tcc1  | CREATE TABLE `tcc1` (
  `c1` int(11) default NULL,
  `c2` int(11) default NULL,
  UNIQUE KEY `c1` (`c1`),
  UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

So, key 2 means unique index on c2.

Hi,

I am testing current latest version "mysql-5.2.0-falcon-alpha-win32" regarding our issue of constraint name not displayed on voilation.

When the foreign key is voileted, I see constraint name displayed in the error message like shown below.

####################
[root@localhost:3306] ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`mydb`.`child`, CONSTRAINT `fk_child` FOREIGN KEY (`id`) REFERENCES `parent` (`id`))
####################

But still if the primary key is voileted I get message

####################
[root@localhost:3306] ERROR 1062: Duplicate entry '3' for key 'PRIMARY'
####################

Will it be possible to display more readable message like

###########
Can not insert duplicate value. Primary constraint pk_parent is voilated for entry '3'"
###########

instead of text "for key 'PRIMARY'"

Thanks

Sameer

1)

On primary key constraint voilation, constraint name is not displayed. Displayed message is

################
[root@localhost:3306] ERROR 1062: Duplicate entry '3' for key 'PRIMARY'
################

will be more useful if constraint name is displayed.

2) When I try to drop a table whos primary key is referenced as foreign key by some other tables, I get message as 

########################
[root@localhost:3306] ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
########################

This message is not user friendly. It will make more sense if the message is displayed as

"unique/primary keys in table referenced by foreign keys"

Thanks

Sameer