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 <http://www.mysql.com/doc/en/CREATE_TABLE.html>. 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 <[EMAIL PROTECTED]> 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
