**None of these are "table level" modifiers**
Sorry, I guess I used the wrong terminology. I am aware of the table options
available. What I meant is “additional definitions” after the columns have been
defined.
Now, do I understand that you are saying that a user can specify any of :
| [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 [INDEX] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) [reference_definition]
| CHECK (expr)
after the column definitions, except for the primary key(if it is already defined )?
Michael Stassen <[EMAIL PROTECTED]> wrote:
Khashan wrote:
> Thanks for the reply.
> It seems like the syntax for create_table/create_definition
> should have 2 part-2 modifiers
I think you are misreading the syntax. That is, I think you are confusing
the index-related create_definitions with the table_options.
> 1- one part-2 'column' modifiers for the each column.
> and
The column modifiers are
[NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
[reference_definition]
So, you can effectively create an index on a column as you define the column
by declaring it as the PRIMARY KEY. (UNIQUE seems to work, as well.)
> 2- another part-2 table modifiers for the table as a whole ?
> ( at the end of column definitions).
> two questions:
> -- Are any of the these modifiers:
> - Primary Key
> - Index
> - KEY
> - Unique
> mutually exclusive at the column or table level?
> ( INDEX and KEY are the same, so only one is allowed)
None of these are "table level" modifiers. They are additional
"create_definitions", similar to the column create definition, except that
they create indexes on already defined columns. The table_options, "table
level modifiers" as you call them, are further down on the manual page
. You cannot create indexes
in the table_options section.
> -- Does it make sense for a user to specify 'Primary Key' as part of
> a column modifiers and then again specify 'Primary Key' as part of
> the table modifiers?
No. You can only have one PRIMARY KEY. That's what primary means.
Michael
> Ahmad Khashan
>
> Rhino wrote:
>
> ----- Original Message -----
> From: "Khashan"
>
>>I am looking at the syntax for creating table( section 6.5.3 of the
>
> manual):
>
>>CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
>
> [(create_definition,...)] [table_options] [select_statement]
>
>>
>>
>>and:
>>
>>create_definition:
>>
>>part-1 (<- inserted by me for reference)
>>
>>col_name type [NOT NULL | NULL] etc...[reference_definition]
>>
>>part-2 (<- inserted by me for reference)
>>
>>| PRIMARY KEY (index_col_name,...)
>>
>>| KEY [index_name] (index_col_name,...)
>>
>>| INDEX [index_name] (index_col_name,...)
>>
>>| UNIQUE [INDEX] [index_name] (index_col_name,...)
>>
>>| FULLTEXT [INDEX] [index_name] (index_col_name,...)
>>
>>| [CONSTRAINT symbol] FOREIGN KEY .. [reference_definition]
>>
>>| CHECK (expr)
>>
>>
>>
>>The syntax suggests that for each column, in addition to specifying
>
> part-1, I can specify any or all of the options in part-2 ???.
>
>>1- Is this true.
>>
>>
>
> Yes.
>
>>2- Can I specify the options in part-2 after I defined all my columns. I
>
> have used things like:
>
>>CREATE TABLE animals (
>>
>>id MEDIUMINT NOT NULL AUTO_INCREMENT,
>>
>>name CHAR(30) NOT NULL,
>>
>>PRIMARY KEY (id)
>>
>>);
>>
>
> Yes, this is the way a CREATE TABLE normally looks: after each column
> name/datatype/null specification ("part 1" in your terminology) come the
> various attributes that further define the column ("part 2" in your
> terminology). A given column can have one or more of these attributes.
>
> Rhino
Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam
