Bug #20563 ISO/IEC 9075:1992 column constraint names are not supported
Submitted: 20 Jun 2006 9:18 Modified: 20 Jun 2006 12:34
Reporter: David Gradwell Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.1 OS:Any (Windows)
Assigned to: CPU Architecture:Any

[20 Jun 2006 9:18] David Gradwell
Description:
ISO/IEC 9075:1992 supports named column constraints as in:

  <column constraint definition>    ::=
         [ <constraint name definition> ] <column constraint> [ <constraint attributes> ]

  <constraint name definition>    ::=   CONSTRAINT <constraint name>

  <constraint name>    ::=   <qualified name>

  <column constraint>    ::=
         NOT NULL
     |     <unique specification>
     |     <references specification>
     |     <check constraint definition> 

The <constraint name definition> is not supported by MySQL (See manual page 743).  Relevant extract:

column_definition:
col_name data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]

Useful reference:

http://savage.net.au/SQL/sql-92.bnf.html#column%20constraint%20definition

How to repeat:
run the following SQL:

create table a
(
	b numeric(1) CONSTRAINT bNotNull NOT NULL
)

and you get: 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT bNotNull NOT NULL
)' at line 3

Suggested fix:
Implement the syntax for 

<constraint name definition>    ::=   CONSTRAINT <constraint name>

as applied to 

<column constraint definition>    ::=
         [ <constraint name definition> ] <column constraint> [ <constraint attributes> ]
[20 Jun 2006 12:34] Valeriy Kravchuk
Thank you for a reasonable feature request.