MySQL 1075 - Incorrect table definition autoincrement vs another key

Question

Here is a table in MySQL 5 3 X  db   CREATE TABLE members       id  int 11   UNSIGNED NOT NULL AUTO INCREMENT     memberid  VARCHAR  30   NOT NULL      Time  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP      firstname  VARCHAR  50   NULL      lastname  VARCHAR  50   NULL     UNIQUE  memberid     PRIMARY KEY  id     ENGINE   MYISAM    Id column is never used in queries  it is just for visual convenience  so it s easy to see how the table grows   Memberid is an actual key  is unique  and memberid is used in queries to identify any member  WHERE memberid  abcde     My question is  how to keep auto increment  but make memberid as a primary key  Is that possible  When I try to create this table with PRIMARY KEY  memberid   I get an error      1075 - Incorrect table definition  there can be only one auto column and it must be defined as a key   What is the best choice  Hopefully  there is a way to keep id column so performance is good and queries identify any user by memberid  not by id   if the performance is very important  although the disk space is not

User · Answer

You can make the id the primary key  and set member id to NOT NULL UNIQUE   Which you ve done   Columns that are NOT NULL UNIQUE can be the target of foreign key references  just like a primary key can    I m pretty sure that s true of all SQL platforms    At the conceptual level  there s no difference between PRIMARY KEY and NOT NULL UNIQUE  At the physical level  this is a MySQL issue  other SQL platforms will let you use a sequence without making it the primary key     But if performance is really important  you should think twice about widening your table by  four bytes per row for that tiny visual convenience   In addition  if you switch to INNODB in order to enforce foreign key constraints  MySQL will use your primary key in a clustered index  Since you re not using your primary key  I imagine that could hurt performance

User · Answer

I think i understand what the reason of your error  First you click auto AUTO INCREMENT field then select it as a primary key      The Right way is First You have to select it as a primary key then you   have to click auto AUTO INCREMENT field    Very easy  Thanks

User · Answer

You can have an auto-Incrementing column that is not the PRIMARY KEY  as long as there is an index  key  on it   CREATE TABLE members      id int 11   UNSIGNED NOT NULL AUTO INCREMENT    memberid VARCHAR  30   NOT NULL       time  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP      firstname VARCHAR  50   NULL      lastname VARCHAR  50   NULL      PRIMARY KEY  memberid      KEY  id                           --- or     UNIQUE KEY  id    ENGINE   MYISAM

User · Answer

First create table without auto increment   CREATE TABLE  members        id  int 11  NOT NULL       memberid  VARCHAR  30   NOT NULL        Time  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP        firstname  VARCHAR  50   NULL        lastname  VARCHAR  50   NULL     PRIMARY KEY  memberid     ENGINE   MYISAM    after set id as index   ALTER TABLE  members  ADD INDEX  id      after set id as auto increment   ALTER TABLE  members  CHANGE  id   id  INT 11  NOT NULL AUTO INCREMENT      Or   CREATE TABLE IF NOT EXISTS  members         id  int 11  NOT NULL       memberid  VARCHAR  30   NOT NULL        Time  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP        firstname  VARCHAR  50   NULL        lastname  VARCHAR  50   NULL        PRIMARY KEY   memberid          KEY  id    id     ENGINE MYISAM DEFAULT CHARSET utf8 AUTO INCREMENT 1

User · Answer

For the above issue  first of all if suppose tables contains more than 1 primary key then first remove all those primary keys and add first AUTO INCREMENT field as primary key then add another required primary keys which is removed earlier  Set AUTO INCREMENT option for required field from the option area

User · Answer

Identified this solution while reading this thread  Figured id post this for the next guy possibly  When dealing with Laravel migration file from a package  I Ran into this issue  My old value was  table- gt increments  id     My new  table- gt integer  id  - gt autoIncrement

[mysql] MySQL: #1075 - Incorrect table definition; autoincrement vs another key?

Examples related to mysql

Examples related to primary-key

Examples related to auto-increment

Examples related to mysql-error-1075