CODEWITHSUNDEEP
sqlmysql
Enrique
Enrique

Reputation: 10117

Generate int primary key manually in mysql

What is the best way to generate a manually int primary key with mysql?

I dont want to use the autoincrement feature.

I taught to create a table with one column of type int named sequence. So every time I'm going to insert a new record first check this column and use the value as the primary key. Then after the insert is successful increment the value of sequence.

Is this a good aproach?

Upvotes: 1

Views: 2577

Answers (3)

Ramon
Ramon

Reputation: 8424

Create a table with one int column of type InnoDB:

CREATE TABLE key_table ( next_value INT NOT NULL DEFAULT 0 ) ENGINE=InnoDB;

Then when you want to get a new value for the key you must start a transaction (using the API you are using) and then:

SELECT next_value FROM key_table FOR UPDATE

Then read the value returned, it is the value for your primary key. Then to increment the value while still in the same transaction execute:

UPDATE key_table SET next_value = next_value + 1;

Then commit your transaction.

This will ensure complete consistency if and only if you use transaction as described above and you use the InnoDB table type.

Upvotes: 6

Learning
Learning

Reputation: 8185

This approach works but you need to have a transaction which wraps these distinct atomic operations (looking up, inserting and incrementing)

However, could you elaborate on why autoincrement column is not good enough? It is safe and guaranteed database feature, so unless you have compelling reasons, I would stick with it.

Upvotes: 2

davek
davek

Reputation: 22915

I think that's very reasonable, as long as the value of the next sequence from the given table is delivered via a function or procedure (that itself updates the sequence table) to ensure uniqueness, and not via

select max(id) + 1 from ...

Upvotes: 0

Related Questions