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

Asked

Viewed 120 times

2

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.

c# .net database

  • "With an auto_increment key, to know the primary key of the record I saved, I will have to go to the database and get it after saving the data." - normally this value is returned by the DB in a safe manner, without significant "cost" when the application makes new insertion. In the case of 2 you don’t have the least control of not creating repeated keys if your more than one application runs, or if your application has more than one thread changing the DB.

1 answer

2


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.

Browser other questions tagged c# .net database

You are not signed in. Login or sign up in order to post.