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