sql - What is wrong with this mysql query?

Question

This 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)
)

...results in:

Error Code: 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

I added primary key but still get error.

Answer 1

Quote Bug #45987, ERROR 1075 (42000): Incorrect table definition:

Actually, this is not a server bug, just a difference between MyISAM (assumed as default by that manual page) and 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 engine; InnoDB is.

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

Conclusion

InnoDB doesn't support AUTO_INCREMENT but no primary key being defined for the table, but MyISAM does. Choose which engine (MyISAM or InnoDB) you want to use, and deal with the CREATE TABLE statement accordingly.