How do I make another MySQL auto increment column?

Question

MySQL doesn't support multiple auto increment columns.

CREATE TABLE IF NOT EXISTS `parts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Is there another solution to make the value of column order increase automatically when I insert a new record?

Answer 1

You can do it from within your application by issuing another query that increases order or you can create a trigger that does that for you. Whatever you do, for the sake of sanity of programming world - don't use reserved words for column names such as order :)

Answer 2

Why do you want 2 fields to be auto incremented - they will have the same values anyway so you can just use ID.

If you want to have your invoices/orders to have sequential numbers then you should keep that numbering in separate table and haves separate logic to update those numbers.

Answer 3

Based on your original table:

CREATE TABLE IF NOT EXISTS `parts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

How about this single insert query:

INSERT INTO `parts` (`name`, `order`) 
SELECT 'name of new part', IFNULL(MAX(`order`), 0) + 1 FROM parts;

If the claim is true that this is not a safe query, one can simply introduce table locking as follows:

LOCK TABLES `parts` AS t1 WRITE, `parts` WRITE;

INSERT INTO `parts` (`name`, `order`) 
SELECT 'name of new part', IFNULL(MAX(`order`), 0) + 1 FROM `parts` AS t1;

UNLOCK TABLES;