How to use 2 auto increment columns in MySQL phpmyadmin

Question

Is it possible to use 2 auto increment values ?

• One starting from 0
• Another starting from 4000400

Please help me

Answer 1

No, you can't. Not out of the box. Possible workarounds:

1. Triggers (an AFTER INSERT trigger).

   Disadvantages:

   • Plain horror. Comes with all the other disadvantages of triggers, like maintenance and debugging nightmares.

   Advantages:

   • You can have FOREIGN KEY constraints that reference this column.
   • You can update the first and the second id columns individually.

2. Views. If you only a value that is always +4000400 of the first auto incremented value, you can use a view, so basically not store this value at all, just calculate it when you need it:

       CREATE VIEW 
           tablex_with_2nd_AI AS
       SELECT 
           tablex_id,
           colA,                                 -- other columns
           --                                    -- you need
           tablex_ix + 4000400  AS second_id
       FROM 
           tablex ;
   

   Disadvantages:

   • You can't have a FOREIGN KEY constraint that references this (virtual) column.

   Advantages:

   • Saves some space. Slightly faster inserts.

3. Don't do that, don't have a second auto incremented column at all. Do you really need a second auto incremented value? What for? A description of the actual problem you are trying to solve would help others help you better. I think you have only told here how you tried to solve a problem and not what the actual problem is.

Answer 2

Have a look at the triggers tab - you should be able to make a trigger to update a 2nd auto increment column based on an insert.

    CREATE TABLE IF NOT EXISTS test2 (
    id          int(11) NOT NULL AUTO_INCREMENT,
    another_id      int(11) ,
    blah        varchar(250),
    PRIMARY KEY (`id`)  
)   ENGINE=MyISAM  DEFAULT CHARSET=latin1 ; 

The trigger should increment the column 'another_id' (which you set to 40004000) when a row is inserted into the table

Answer 3

I have good news and bad news

GOOD NEWS

You can multiple auto_increment values

BAD NEWS

Two things:

1. It is only available for the MyISAM Storage Engine
2. Only one numeric auto_increment value is allowed. Each auto_increment must be have associated column to define uniqueness from other auto_increment values within the same table.

I have discussed this before

• Jun 10, 2012 : MySQL get next unique value without auto increment
• Apr 21, 2012 : How can you have two auto-incremental columns in one table?

Here is a sample table

USE test
DROP TABLE IF EXISTS stores;
CREATE TABLE stores
(
    store_type int not null,
    id int not null auto_increment,
    store_name varchar(128) not null,
    PRIMARY KEY (store_type,id)
) ENGINE=MyISAM;

Here is sample data

INSERT INTO stores (store_type,store_name) VALUES
(1,'Red Lobster'),(1,'Olive Garden'),
(2,'ShopRite'),(2,'PathMark'),(2,'Wegman''s'),
(3,'McDonald''s'),(3,'Wendy''s'),(3,'Burger King'),
(1,'Ruby Tuesdays'),(1,'TGI Fridays'),
(4,'BJs'),(4,'Costco'),(1,'Bennigan''s');

Let's load it

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS stores;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE stores
    -> (
    ->     store_type int not null,
    ->     id int not null auto_increment,
    ->     store_name varchar(128) not null,
    ->     PRIMARY KEY (store_type,id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO stores (store_type,store_name) VALUES
    -> (1,'Red Lobster'),(1,'Olive Garden'),
    -> (2,'ShopRite'),(2,'PathMark'),(2,'Wegman''s'),
    -> (3,'McDonald''s'),(3,'Wendy''s'),(3,'Burger King'),
    -> (1,'Ruby Tuesdays'),(1,'TGI Fridays'),
    -> (4,'BJs'),(4,'Costco'),(1,'Bennigan''s');
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql>

Now, look at the data:

mysql> SELECT * FROM stores;
+------------+----+---------------+
| store_type | id | store_name    |
+------------+----+---------------+
|          1 |  1 | Red Lobster   |
|          1 |  2 | Olive Garden  |
|          2 |  1 | ShopRite      |
|          2 |  2 | PathMark      |
|          2 |  3 | Wegman's      |
|          3 |  1 | McDonald's    |
|          3 |  2 | Wendy's       |
|          3 |  3 | Burger King   |
|          1 |  3 | Ruby Tuesdays |
|          1 |  4 | TGI Fridays   |
|          4 |  1 | BJs           |
|          4 |  2 | Costco        |
|          1 |  5 | Bennigan's    |
+------------+----+---------------+
13 rows in set (0.00 sec)

mysql>

Now, look at the data ordered by the PRIMARY KEY columns

mysql> SELECT * FROM stores ORDER BY store_type,id;
+------------+----+---------------+
| store_type | id | store_name    |
+------------+----+---------------+
|          1 |  1 | Red Lobster   |
|          1 |  2 | Olive Garden  |
|          1 |  3 | Ruby Tuesdays |
|          1 |  4 | TGI Fridays   |
|          1 |  5 | Bennigan's    |
|          2 |  1 | ShopRite      |
|          2 |  2 | PathMark      |
|          2 |  3 | Wegman's      |
|          3 |  1 | McDonald's    |
|          3 |  2 | Wendy's       |
|          3 |  3 | Burger King   |
|          4 |  1 | BJs           |
|          4 |  2 | Costco        |
+------------+----+---------------+
13 rows in set (0.00 sec)

mysql>

Give it a Try !!!

UPDATE 2013-02-26 12:00 EST

Let's augment the SuperMarkets (store_type 2) by add 4000400 to the id values

mysql> UPDATE stores SET id = id + 4000400 WHERE store_type = 2;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM stores ORDER BY store_type,id;
+------------+---------+---------------+
| store_type | id      | store_name    |
+------------+---------+---------------+
|          1 |       1 | Red Lobster   |
|          1 |       2 | Olive Garden  |
|          1 |       3 | Ruby Tuesdays |
|          1 |       4 | TGI Fridays   |
|          1 |       5 | Bennigan's    |
|          2 | 4000401 | ShopRite      |
|          2 | 4000402 | PathMark      |
|          2 | 4000403 | Wegman's      |
|          3 |       1 | McDonald's    |
|          3 |       2 | Wendy's       |
|          3 |       3 | Burger King   |
|          4 |       1 | BJs           |
|          4 |       2 | Costco        |
+------------+---------+---------------+
13 rows in set (0.00 sec)

mysql>

Let's add A & P as another SuperMarket

mysql> INSERT INTO stores (store_type,store_name) VALUES (2,'A & P');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM stores ORDER BY store_type,id;
+------------+---------+---------------+
| store_type | id      | store_name    |
+------------+---------+---------------+
|          1 |       1 | Red Lobster   |
|          1 |       2 | Olive Garden  |
|          1 |       3 | Ruby Tuesdays |
|          1 |       4 | TGI Fridays   |
|          1 |       5 | Bennigan's    |
|          2 | 4000401 | ShopRite      |
|          2 | 4000402 | PathMark      |
|          2 | 4000403 | Wegman's      |
|          2 | 4000404 | A & P         |
|          3 |       1 | McDonald's    |
|          3 |       2 | Wendy's       |
|          3 |       3 | Burger King   |
|          4 |       1 | BJs           |
|          4 |       2 | Costco        |
+------------+---------+---------------+
14 rows in set (0.00 sec)

mysql>

Looks like it can work for you !!!