Two autoincrements columns or autoincrement and sa

Question

I need two columns in table that would have same value on insert. Is there any way to do it from database side?

Answer 1

Define a before or after insert trigger and assign the value of the 2nd field in the trigger.

If the 1st field is an auto increment column, then you need to use an after insert trigger. If your application assigns value to the 1st field, then you can use a before insert trigger.

However, I would no necessarily duplicate the value on insert. You can leave the 2nd field as null on insert, which would mean that its value is the same as the 1st field's. The only drawback of this approach is that it may be more difficult to create joins on the 2nd field.

Answer 2

So you want to let one column use the auto_increment feature, but make another column in the same table also have the same value?

I can't think of a reason you would need this feature. Perhaps you could explain what you're trying to accomplish, and I can suggest a different solution?

A trigger won't work for this. It's a chicken-and-egg problem:

• You can't change any column's value in an AFTER trigger.
• But the auto-increment value isn't set yet when a BEFORE trigger executes.

It also won't work to use a MySQL 5.7 GENERATED column:

CREATE TABLE MyTable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  why_would_you_want_this INT GENERATED ALWAYS AS (id)
);

ERROR 3109 (HY000): Generated column 'why_would_you_want_this' 
cannot refer to auto-increment column.

You can't do it in a single SQL statement. You have to INSERT the row, and then immediately do an UPDATE to set your second column to the same value.

CREATE TABLE MyTable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  why_would_you_want_this INT
);

INSERT INTO MyTable () VALUES ();

UPDATE MyTable SET why_would_you_want_this = LAST_INSERT_ID() 
WHERE id = LAST_INSERT_ID();

You could alternatively generate the ID value using some other mechanism besides AUTO_INCREMENT (for example a Memcached incrementing key). Then you could insert the new value in both columns:

INSERT INTO MyTable (id, why_would_you_want_this) VALUES ($gen_id, $gen_id);