Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to use 2 auto increment values ?

  • One starting from 0
  • Another starting from 4000400

Please help me

share|improve this question
    
have you actually experimented to see if you can? To start from a value other than one w3schools.com/sql/sql_autoincrement.asp –  Gaz_Edge Feb 26 '13 at 13:22
    
@RolandoMySQLDBA thanks –  user2106221 Feb 27 '13 at 4:03
1  
@user2106221 Please give us clarification on what you are asking. Sample rows would be good. You now have 3 answers from 2 different interpretations of your question. I would like to remove my answer if I haven't understood what you are asking. –  ypercube Feb 27 '13 at 10:36
add comment

3 Answers

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.

share|improve this answer
add comment

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

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 !!!

share|improve this answer
    
It's obvious we have interpreted the question differently. Lets see what the OP has to say. –  ypercube Feb 26 '13 at 16:43
add comment

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

share|improve this answer
    
your question is not clear. Please try re-wording. You will get a better response with a well worded question –  Gaz_Edge Feb 26 '13 at 13:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.