MySQL: #1075 – Incorrect table definition; autoincrement vs another key?

by

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;

More Related Contents:

  1. Remove Primary Key in MySQL
  2. make an ID in a mysql table auto_increment (after the fact)
  3. How to reset AUTO_INCREMENT in MySQL
  4. How to make MySQL table primary key auto increment with some prefix
  5. Auto Increment after delete in MySQL
  6. Get the new record primary key ID from MySQL insert query?
  7. How to properly create composite primary keys – MYSQL
  8. Is there a REAL performance difference between INT and VARCHAR primary keys?
  9. MySQL AUTO_INCREMENT does not ROLLBACK
  10. difference between primary key and unique key
  11. Difference between Key, Primary Key, Unique Key and Index in MySQL
  12. SQL: Creating a Relational table with 2 different auto_increment
  13. How to handle fragmentation of auto_increment ID column in MySQL
  14. Prevent auto increment on MySQL duplicate insert
  15. auto increment primary leaving gaps in counting
  16. How to fill in the “holes” in auto-increment fields?
  17. ALTER TABLE to add a composite primary key
  18. How to insert data to MySQL with auto-incremented column(field)?
  19. auto_increment by group
  20. Why does MySQL autoincrement increase on failed inserts?
  21. MySQL InnoDB: autoincrement non-primary key
  22. How to force MySQL to take 0 as a valid auto-increment value
  23. How to make a primary key start from 1000?
  24. Defining Composite Key with Auto Increment in MySQL
  25. mysql Failed to read auto-increment value from storage engine
  26. Too many auto increments with ON DUPLICATE KEY UPDATE
  27. How to reuse auto_increment values? [duplicate]
  28. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  29. Multi-Column Primary Key in MySQL 5
  30. Composite Primary Key performance drawback in MySQL

Leave a Comment