What is wrong with this mysql query?

Question

It works:

CREATE TABLE shoutbox_shout ( shout_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL DEFAULT 0, shout_date INT UNSIGNED NOT NULL DEFAULT 0, message MEDIUMTEXT NOT NULL, KEY shout_date (shout_date) ) 

... while this:

 CREATE TABLE shoutbox_shout ( shout_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL DEFAULT 0, shout_date INT UNSIGNED NOT NULL DEFAULT 0, message MEDIUMTEXT NOT NULL, KEY shout_date (shout_date) ) 

... leads to:

Error code: 1075 - invalid table definition; there can only be one automatic column, and it must be defined as a key

I added the primary key, but still getting the error.

Answer 1

Quote Error # 45987, ERROR 1075 (42000): Invalid table definition :

Actually, this is not a server error, just the difference between MyISAM (accepted by default on this manual page) and the InnoDB storage engine, documented elsewhere: http://dev.mysql.com/doc/refman/5.1/en/ innodb-auto-increment-handling.html

MyISAM is no longer the default mechanism; InnoDB is.

Also: http://bugs.mysql.com/bug.php?id=60104

Conclusion

InnoDB does not support AUTO_INCREMENT, but the primary key is not defined for the table, but MyISAM does. Choose which engine (MyISAM or InnoDB) you want to use, and process the CREATE TABLE statement accordingly.

Answer 2

Well, I'm not a mysql expert, but

shout_id INT UNCERTAIN NOT NULL. AUTO_INCRIMENT

is an automatic increment not defined as Key ....

Given the many obscure or misleading error messages we experience in this business, I cannot present a more clear error message ....

Answer 3

+1 for using unsigned etc. but you say ... it may not be null ... BUT its default is 0 .. and for me always 0? and thats auto_increment primary key is NEVER null. Your pretty one in a good direction, but do it like this:

 create table shoutbox_shout ( shout_id int unsigned auto_increment primary key, user_id int unsigned not null, shout_date datetime, message mediumtest not null )engine=innodb; 

Answer 4

@Brandon_R: I get the same error using the MySQL 5.0.67-community-nt command line using code similar to this, though -

 CREATE TABLE shoutbox_shout ( shout_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL DEFAULT 0, shout_date INT UNSIGNED NOT NULL DEFAULT 0, message MEDIUMTEXT NOT NULL, KEY shout_date (shout_date), PRIMARY KEY (shout_id) ); 

Answer 5

You MUST define the auto_increment column as the primary key / key

 CREATE TABLE shoutbox_shout ( shout_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL DEFAULT 0, shout_date INT UNSIGNED NOT NULL DEFAULT 0, message MEDIUMTEXT NOT NULL, KEY shout_date (shout_date), primary key (shout_id) -- primary key ) 

or

 CREATE TABLE shoutbox_shout ( shout_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL DEFAULT 0, shout_date INT UNSIGNED NOT NULL DEFAULT 0, message MEDIUMTEXT NOT NULL, KEY shout_date (shout_date), key (shout_id) -- key ) 

You should also determine your engine type - I would recommend innodb.

It's nice to see you use unsigned integer data types without specifying a silly extra screen width!

Answer 6

Does this error explain briefly? The AUTO_INCREMENT column - if you have one - should be the key column.

As you demonstrate, this fixes the problem.