Mysql – How to use 2 auto increment columns in MySQL phptheadmin

auto-incrementMySQLphpmyadmin

Is it possible to use 2 auto increment values ?

  • One starting from 0
  • Another starting from 4000400

Please help me

Best Answer

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.

Related Question