syntax problem for CREATE TABLE ??

Baoqiu Cui wrote:

 > What bothers me is that I just don't see how the CREATE TABLE syntax
 > allows the UNIQUE keyword to appear in a COLUMN_DEFINITION:
 >
 > COLUMN_DEFINITION:
 > COL_NAME TYPE [NOT NULL | NULL] [DEFAULT DEFAULT_VALUE]
 > [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'STRING']
 > [REFERENCE_DEFINITION]

You seem to have copied only the lower part of the definition. Read it
all and you should find the UNIQUE also:

<a rel="nofollow" style='text-decoration: none;' href="http://dev.mysql.com/doc/mysql/en/create-table.html" target="_blank">http://dev.mysql.com/doc/mysql/en/create-table.html</a>

------------------------
create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| KEY [index_name] [index_type] (index_col_name,...)
| INDEX [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE[INDEX]
[index_name] [index_type] (index_col_name,...)
| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) [reference_definition]
| CHECK (expr)
column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
[reference_definition]
------------------------
 >> Stay informed about: syntax problem for CREATE TABLE ?? 

Aggro writes:

 > Baoqiu Cui wrote:
 >
  >> What bothers me is that I just don't see how the CREATE TABLE syntax
  >> allows the UNIQUE keyword to appear in a COLUMN_DEFINITION:
  >> COLUMN_DEFINITION:
  >> COL_NAME TYPE [NOT NULL | NULL] [DEFAULT DEFAULT_VALUE]
  >> [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'STRING']
  >> [REFERENCE_DEFINITION]
 >
 > You seem to have copied only the lower part of the definition. Read it
 > all and you should find the UNIQUE also:
 >
<font color=purple> > <a rel="nofollow" style='text-decoration: none;' href="http://dev.mysql.com/doc/mysql/en/create-table.html</font" target="_blank">http://dev.mysql.com/doc/mysql/en/create-table.html</font</a>>

The first part, which contains UNIQUE, is NOT for a COLUMN_DEFINITION.
I know I can do the following CREATE TABLE:

mysql> create table t1 (c1 int, unique (c1));
Query OK, 0 rows affected (0.06 sec)

but it is different from "create table t1 (c1 int unique)". I want to
find the syntax to allow the latter.

--
Baoqiu Cui <cbaoqiu at yahoo.com>
 >> Stay informed about: syntax problem for CREATE TABLE ?? 

Bill Karwin writes:

 > It could just be an omission from the documentation. On the doc page
 > for create table, I find this paragraph:
 >
 > "From MySQL 4.1.0 on, the attribute SERIAL can be used as an alias for
 > BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE."
 >
 > That sort of implies that UNIQUE can appear in that position, similar
 > to PRIMARY KEY.

That is what I thought. Maybe the CREATE TABLE syntax should be changed
to explicitly allow UNIQUE in COLUMN_DEFINITION, because many people
(and many exiting MySQL examples) are using UNIQUE in this way.

--
Baoqiu Cui <cbaoqiu at yahoo.com>
 >> Stay informed about: syntax problem for CREATE TABLE ??