CODEWITHSUNDEEP
mysqlsqldatabase
bLAZ
bLAZ

Reputation: 1751

Use auto incremented ID in another column

I have two columns in my DB table, where the first one is auto incremented ID. In the second one I would like to have that ID mirrored. (I know it sound like design error, but I really need it)

Is it possible to configure it that way?

Upvotes: 1

Views: 211

Answers (3)

bortunac
bortunac

Reputation: 4808

use TRIGGER

DELIMITER |:    # switch delimiter to prevent execution of  ;

CREATE TRIGGER `copy_id2c_name` BEFORE INSERT ON tb_name
FOR EACH ROW BEGIN
SELECT AUTO_INCREMENT INTO @AI FROM information_schema.tables WHERE table_schema = 'db_name' and table_name = 'tb_name';
set NEW.c_name = @AI;
END;

|:   # execute code
DELIMITER ;   // switch back original delimiter

Upvotes: 1

jussius
jussius

Reputation: 3274

If you're using MySql 5.7.6 or later you can define the second id as a virtual column like this:

CREATE TABLE my_table (
    id INT UNSIGNED AUTO_INCREMENT,
    id_mirrored INT UNSIGNED AS (id)
);

This way the id_mirrored column isn't really stored in your database but instead it's evaluated when the row is read.

If you're using an earlier version of MySql creating a view is probably your best option. Views are basically virtual tables.

CREATE VIEW my_view AS 
    SELECT
        t.id AS id,
        t.id AS id_mirrored
    FROM my_table t

Third option is to define the id_mirrored as a real column and add a trigger to give it it's value. The way to do this has already been described in other answers.

Upvotes: 2

Aleksej Vasinov
Aleksej Vasinov

Reputation: 2797

You could use smth like LAST_INSERT_ID() in case you are using stored procedure.

Upvotes: 0

Related Questions