[Fou-Lu]

Originally Posted by Old Pedant

Not true! AUTO_INCREMENT requires that the AUTO_INCREMENT column have a a UNIQUE INDEX, but it does *NOT* have to be the primary key.

Example:

mysql> CREATE TABLE FouLu ( id INT AUTO_INCREMENT,
    -> email VARCHAR(50) PRIMARY KEY,
    -> UNIQUE INDEX id_index (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> show create table FouLu;
+-------+----------------------------------------------------
| Table | Create Table
+-------+----------------------------------------------------
| FouLu | CREATE TABLE `foulu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`email`),
  UNIQUE KEY `id_index` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------

I grant you that this is not normal usage, but it's perfectly legal.

I'll be damned, this whole time I thought that you required PK for ai in mysql!
Would there be any benefit of doing the above instead versus swapping the PK and UK on that table? I typically don't use AI at all as I don't usually use a surrogate key, but this may become beneficial in the future for an alternate field I would want to increment.