CODEWITHSUNDEEP
mysqlauto-increment
Basem
Basem

Reputation: 442

How do I make another MySQL auto increment column?

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?

Upvotes: 6

Views: 35488

Answers (7)

Ergest Basha
Ergest Basha

Reputation: 8978

For what is worth, if anybody need this again you can use this trigger. It increment order by +1 based on id and it works on multiple insert values

DELIMITER $$
CREATE TRIGGER MyTrigger BEFORE INSERT ON parts
FOR EACH ROW
BEGIN
    IF NEW.`order` < 0 THEN
        SET NEW.`order` = (select id from parts order by id desc limit 1) +1;
    ELSEIF NEW.`order` > 0 THEN
        SET NEW.`order` = (select id from parts order by id desc limit 1) +1;
    ELSEIF NEW.`order` is null THEN
        SET NEW.`order` = (select id from parts order by id desc limit 1) +1;
    END IF;
END $$
DELIMITER ;

Upvotes: 0

loomcomputer
loomcomputer

Reputation: 1

Don't think too far _ just add the last_id to your order String and thats it :) I solved it this way that I added a string let's say "Order_2018_00" + LAST_INSERT_ID(). So this field is unique for each added set :)

Upvotes: -1

Riho
Riho

Reputation: 4593

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.

Upvotes: 3

HenrikW
HenrikW

Reputation: 106

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;

Upvotes: 3

sanmai
sanmai

Reputation: 30881

Consider adding a second table for the order column:

CREATE TABLE IF NOT EXISTS `parts_order` (
  `order` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`order`)
) ENGINE=MyISAM;

Example usage:

-- First we insert a new empty row into `parts_order`
INSERT INTO `parts_order` VALUES (NULL);
-- Next we insert a new row into `parts` with last inserted id from `parts_order`
INSERT INTO `parts` SET `name` = "new name", `order` = LAST_INSERT_ID();

This approach does not require transactions nor locking.

Upvotes: -1

Devart
Devart

Reputation: 121902

I'd suggest you to set only order field as AUTO_INCREMENT; and calculate new value for the id field manually. Here it is an example -

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

-- Add some new rows with manually auto-incremented id:

--    SELECT COALESCE(MAX(id), 0) + 1 INTO @next_id FROM parts;
--    INSERT INTO parts VALUES(@next_id, '', NULL);
--    SELECT COALESCE(MAX(id), 0) + 1 INTO @next_id FROM parts;
--    INSERT INTO parts VALUES(@next_id, '', NULL);

  INSERT INTO parts SELECT COALESCE(MAX(id), 0) + 1, '', NULL FROM parts;
  INSERT INTO parts SELECT COALESCE(MAX(id), 0) + 1, '', NULL FROM parts;

SELECT * FROM parts;
+----+------+-------+
| id | name | order |
+----+------+-------+
|  1 |      |     1 |
|  2 |      |     1 |
+----+------+-------+


-- Add some new rows for specified `id`, the value for `order` field will be set automatically:

INSERT INTO parts VALUES(2, '', NULL);
INSERT INTO parts VALUES(2, '', NULL);

+----+------+-------+
| id | name | order |
+----+------+-------+
|  1 |      |     1 |
|  2 |      |     1 |
|  2 |      |     2 |
|  2 |      |     3 |
+----+------+-------+

Upvotes: 1

N.B.
N.B.

Reputation: 14060

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 :)

Upvotes: 7

Related Questions