Thanks a lot, I think I have enough to validate the syntax (sort of). Ahmad Khashan
Michael Stassen <[EMAIL PROTECTED]> wrote: Khashan wrote: > **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. Sorry I misunderstood you. > 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 )? Sort of. You can mix column and index definitions, so long as the columns referenced in the index creation are defined. Order doesn't matter. For example: mysql> CREATE TABLE keytest (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> INDEX idn (id, name), -> name CHAR(10) NOT NULL, flag INT); Query OK, 0 rows affected (0.01 sec) mysql> show create table keytest; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | keytest | CREATE TABLE `keytest` ( `id` int(11) NOT NULL auto_increment, `name` char(10) NOT NULL default '', `flag` int(11) default NULL, PRIMARY KEY (`id`), KEY `idn` (`id`,`name`) ) TYPE=MyISAM | (I'm using 4.0.17.) Note that you can define an INDEX before you are done defining COLUMNs, and you can even include a column you haven't defined yet in an index, so long as you do define it. Note also that if you ask MySQL for the create definitions, it lists the indexes, including the primary key, after the columns. Michael Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam
