syntax problem for CREATE TABLE ??

[Baoqiu Cui]

Hi,

I was playing with MySQL (4.1.9) during the weekend, but noticed a minor
problem and would like someone to explain this to me.

Basically I could successfully create a table with one unique column
like this:

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

What bothers me is that I just don't see how the CREATE TABLE syntax
allows the UNIQUE keyword to appear in a COLUMN_DEFINITI ON:

COLUMN_DEFINITI ON:
COL_NAME TYPE [NOT NULL | NULL] [DEFAULT DEFAULT_VALUE]
[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'STRING']
[REFERENCE_DEFIN ITION]

The only explanation I can think of is that this way of declaring a
column unique was supported in some old release and the current version
of MySQL is just trying to keep the backwards compatibility (like SERIAL
column data type).

Does anyone know the real reason? Any input will be appreciated!

- Baoqiu

--
Baoqiu Cui <cbaoqiu at yahoo.com>

[Aggro]

Re: syntax problem for CREATE TABLE ??

Baoqiu Cui wrote:
[color=blue]
> What bothers me is that I just don't see how the CREATE TABLE syntax
> allows the UNIQUE keyword to appear in a COLUMN_DEFINITI ON:
>
> COLUMN_DEFINITI ON:
> COL_NAME TYPE [NOT NULL | NULL] [DEFAULT DEFAULT_VALUE]
> [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'STRING']
> [REFERENCE_DEFIN ITION][/color]

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

http://dev.mysql.com/doc/mysql/en/create-table.html

------------------------
create_definiti on:
column_definiti on
| [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|SPATIA L] [INDEX] [index_name] (index_col_name ,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name ,...) [reference_defin ition]
| CHECK (expr)
column_definiti on:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
[reference_defin ition]
------------------------

[Bill Karwin]

Re: syntax problem for CREATE TABLE ??

Baoqiu Cui wrote:[color=blue]
> mysql> create table t1 (c1 int unique);
> Query OK, 0 rows affected (0.08 sec)
>
> What bothers me is that I just don't see how the CREATE TABLE syntax
> allows the UNIQUE keyword to appear in a COLUMN_DEFINITI ON:[/color]

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.

Regards,
Bill K.

[Baoqiu Cui]

Re: syntax problem for CREATE TABLE ??

Aggro <spammerdream@y ahoo.com> writes:
[color=blue]
> Baoqiu Cui wrote:
>[color=green]
>> What bothers me is that I just don't see how the CREATE TABLE syntax
>> allows the UNIQUE keyword to appear in a COLUMN_DEFINITI ON:
>> COLUMN_DEFINITI ON:
>> COL_NAME TYPE [NOT NULL | NULL] [DEFAULT DEFAULT_VALUE]
>> [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'STRING']
>> [REFERENCE_DEFIN ITION][/color]
>
> You seem to have copied only the lower part of the definition. Read it
> all and you should find the UNIQUE also:
>
> http://dev.mysql.com/doc/mysql/en/create-table.html[/color]

The first part, which contains UNIQUE, is NOT for a COLUMN_DEFINITI ON.
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>

[Baoqiu Cui]

Re: syntax problem for CREATE TABLE ??

Bill Karwin <bill@karwin.co m> writes:
[color=blue]
> 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.[/color]

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

--
Baoqiu Cui <cbaoqiu at yahoo.com>