Adding autoincrement column

[ijakparov]

I need some id column with AI, so it must be primary. Can I make session_id UNIQUE? Will that brake some base module logic?

[chill117]

I think it should still work. You could try it with a test database and then run the tests - to be sure.

[ijakparov]

I think it should still work. You could try it with a test database and then run the tests - to be sure.

It's increase AI value on session update

[chill117]

I found a nice answer on stackoverflow that explains this behavior in MySQL:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)

You should not depend on auto_increment having no gaps. If that is a requirement, the overhead on the updates and inserts is much larger. Essentially, you need to put a lock on the entire table, and renumber everything that needs to be renumbered, typically using a trigger. A better solution is to calculate incremental values on output.