Generate the primary key in the application or keep it auto-incrementable?

Question

Should I set the primary key of my table as auto_increment or define it as numerical, generating the key within my application?

Contextualization

I am developing a C# application and testing it in an Access database, then passing it to a Mysql database.

One problem that crossed my mind was:

1) With a key of the type auto_increment, to know the primary key of the record I saved, I will have to go to the bank and get it after saving the data.

2) With a manual key (created by the application), I will not need to do step 1), as I will have already created the primary key within the application before saving the data.

Answer 1

Should I set the primary key of my table as auto_increment or define it as numerical, generating the key within my application?

In the database always.

1) With a key of the type auto_increment, to know the primary key of the record I saved, I will have to go to the bank and get it after saving the data.

Perfect, that’s the way it should be. Something like that. It’s simple, reliable and fast.

The original question talked about taking the last created ID and incrementing it manually. It is not easy to do this right and can cause a time penalty. If you do wrong you may have a running condition.

2) With a manual key (created by the application), I will not need to do step 1), as I will have already created the primary key within the application before saving the data.

And you can create a key that has already been created by another client. Don’t do this.

You can even do use GUID or something similar, but it’s a complication that only pays off in very specific cases. It’s not easy to do right.