List:General Discussion« Previous MessageNext Message »
From:Jaime Crespo Rincón Date:January 25 2010 3:50pm
Subject:Re: auto_increment without primary key in innodb?
View as plain text  
2010/1/25 Yang Zhang <yanghatespam@stripped>:
> Right, I saw the docs. I'm fine with creating an index on it, but the
> only way I've successfully created a table with auto_increment is by
> making it a primary key. And I still don't understand why this
> requirement is there in the first place.

Non-primary key works for me, as documented:

---------------------------------->8--------------------------------
mysql> create table test_ai (i int PRIMARY KEY, c int auto_increment, index(c));
Query OK, 0 rows affected (0,07 sec)

mysql> desc test_ai;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| i     | int(11) | NO   | PRI | NULL    |                |
| c     | int(11) | NO   | MUL | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0,00 sec)

mysql> insert into test_ai (i) values (100), (200);
Query OK, 2 rows affected (0,00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_ai;
+-----+---+
| i   | c |
+-----+---+
| 100 | 1 |
| 200 | 2 |
+-----+---+
2 rows in set (0,00 sec)
---------------------------------->8--------------------------------

Regards,
-- 
Jaime Crespo
MySQL & Java Instructor
Warp Networks
<http://warp.es>
Thread
auto_increment without primary key in innodb?Yang Zhang25 Jan
  • RE: auto increment without primary key in innodb?Tom Worster25 Jan
    • Re: auto_increment without primary key in innodb?Yang Zhang25 Jan
      • Re: auto_increment without primary key in innodb?Jaime Crespo Rincón25 Jan
      • Re: auto_increment without primary key in innodb?Paul DuBois25 Jan
  • Re: auto_increment without primary key in innodb?Yong Lee26 Jan
    • Re: auto_increment without primary key in innodb?Johan De Meersman26 Jan