CODEWITHSUNDEEP
mysqlsqlmysql-error-1075
BRampersad
BRampersad

Reputation: 862

What is wrong with this mysql query?

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.

Upvotes: 4

Views: 13098

Answers (6)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385405

Doesn't the error explain this succinctly? The AUTO_INCREMENT column — if you have one — must be a key column.

As you demonstrate, making it so fixes the problem.

Upvotes: 1

Jon Black
Jon Black

Reputation: 16569

you MUST define an auto_increment column as a 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 define your engine type - i would recommend innodb.

Nice to see you using unsigned integer datatypes without specifying silly optional display widths !!

Upvotes: 1

stealthyninja
stealthyninja

Reputation: 10371

@Brandon_R: I get the same error using command line of MySQL 5.0.67-community-nt, using the code like this works 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)
);

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332771

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.

Upvotes: 7

Yoram de Langen
Yoram de Langen

Reputation: 5509

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

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;

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146603

Well I'm not a mysql expert, but

shout_id INT UNSIGNED NOT NULL AUTO_INCREMENT

is an auto increment, not defined as Key....

Given the many unclear or misleading error messages we experience in this business, I can't imagine a clearer error message ....

Upvotes: 2

Related Questions