Generate the primary key in the application or keep it as an auto incremental?

Question

Generate the primary key in the application or keep it as an auto incremental?

Navigation

#1 by (1 votes)

1

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

Contextualization

I'm developing a C # application and testing it in an Access database, and then passing it to a MySQL database.

One problem that crossed my mind was:

1) With a key of type auto_increment , to know the primary key of the registry that I saved, I will have to go to the database and fetch it after saving the data.

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

c# .net

asked by anonymous 13.11.2016 / 17:37

1 answer

How to include content from a page .py on another page .py How to create a "back" link

score 1 · Accepted Answer

Should I set the primary key of my table to auto_increment or set it to numeric, generating the key within my application?

In the database always.

1) With an auto_increment key, to know the primary key of the registry that I saved, I will have to go to the database and fetch it after saving the data.

Perfect, this is how it should be. Something like that . It's simple, reliable and fast.

In the original question I was talking about getting the last created ID and incrementing it manually. It is not easy to do this right and it can cause a time penalty. If you do wrong you may have a race condition .

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

And you can create a key that was already created by another client. Do not do that.

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