Bug #21403 more specific SQL states for 23nnn
Submitted: 1 Aug 2006 19:21
Reporter: Matthew Lord Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S4 (Feature request)
Version:all OS:Any (all)
Assigned to: CPU Architecture:Any

[1 Aug 2006 19:21] Matthew Lord 
Description:
Right now we group all 23nnn codes as 23000 when we could be more specific by 
using subcodes.  Here's an example list:

Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY)
Message: Can't write; duplicate key in table '%s'

Error: 1048 SQLSTATE: 23000 (ER_BAD_NULL_ERROR)
Message: Column '%s' cannot be null

Error: 1052 SQLSTATE: 23000 (ER_NON_UNIQ_ERROR)
Message: Column '%s' in %s is ambiguous

Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)
Message: Duplicate entry '%s' for key %d

Error: 1169 SQLSTATE: 23000 (ER_DUP_UNIQUE)
Message: Can't write, because of unique constraint, to table '%s'

Error: 1216 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW)
Message: Cannot add or update a child row: a foreign key constraint fails

Error: 1217 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED)
Message: Cannot delete or update a parent row: a foreign key constraint fails

Error: 1451 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED_2)
Message: Cannot delete or update a parent row: a foreign key constraint fails (%s)

Error: 1452 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW_2)
Message: Cannot add or update a child row: a foreign key constraint fails (%s)

How to repeat:
n/a

Suggested fix:
We could change the SQLSTATE
values in class 23. A common list (possibly ultimately
from X/Open) is:
23000 INTEGRITY CONSTRAINT VIOLATION
23001 RESTRICT VIOLATION
23502 NOT NULL VIOLATION
23503 FOREIGN KEY VIOLATION
23505 UNIQUE VIOLATION
23514 CHECK VIOLATION
[3 Aug 2006 14:56] Matthew Lord 
I just wanted to note that class 23nnn was used as an example.  It would be great if we could apply this logic to all the various SQL state classes.
[10 Oct 2006 16:20] Alexandre Lima 
what I can change the sqlstate?
[3 May 2010 11:37] Chris Wilson 
Error: 1052 SQLSTATE: 23000 (ER_NON_UNIQ_ERROR)
Message: Column '%s' in %s is ambiguous

This is an error in the query. It has nothing to do with a constraint violation of any kind below the SQL syntax level. It can even happen in SELECT queries.

I'd say that reporting this as a constraint violation (SQL state 23000) is a bug. Possible more accurate SQL state codes would be:

42000   	 Syntax error or access violation 
42S22   	 Column not found