Is it necessary to create a primary key with auto increment?

Asked

Viewed 1,476 times

4

When I create a table that already has a field with an index unique, that will be referenced by other tables, can I leave it as primary key? Or is it good practice to create another field, with auto increment, for this purpose?

For example: In an employee table, with the field name and registration, where this will be of type varchar, will have unique values, and will be referenced by other tables, of the following alternatives, which is the most correct? And why?

  1. Only leave the registration field as the primary key.
  2. Leave the registration field with an index unique and create an id field with auto increment to be the primary key.
  3. Leave the registration field as the primary key and create an id field with index auto increment, to assist.
database modeling primary-key índices

3 answers

7


You want to know if it’s better to use one natural key or substitute (surrogate).

Only leave the registration field as the primary key.

That’s a natural key. Do you control that number? Will it ever be changed? Is there not the slightest chance that the same object in the database one day needs to have another unique identification? Is it short enough? Doesn’t it give room for error? Will it always be unique? It really identifies what the object is?

Most existing data in the world do not meet all these criteria, so a replacement key is chosen. If you take all this, you can use it without problems. But be careful, many people think you answer and one day find that you do not answer, it was something circumstantial.

Leave the registration field with an index unique and create an id field with auto increment to be the primary key.

This is a substitute key. It is interesting in many cases to maintain an internal control in the system regardless of the number the user reads. I can’t say I’m the best in your case. If you can do the previous item well then you don’t have to do it, you’re just wasting space and performance.

Leave the registration field as the primary key and create a field id indexed auto increment, to assist.

Doesn’t seem like much of an advantage to me.

Contrary to what many people think there is always a primary key in every table. Even when the database does not require you to create one (it creates without you knowing). Also contrary to popular belief, the primary key is always an index. Data is always sorted in the database by the primary key. Secondary indices usually refer to the primary index.

  • Thank you very much, my doubt has been fully clarified.

2

1) Theoretically would have no problem in doing so, it depends on how your app will handle this PK. It will have additional code fragments to validate whether PK is unique, valid, etc. But it would not be well seen by most Dev’s.

2) Indices are to tunnar the basis in relation to searches and not to manage Primary Keys.

3) By doing so you would be adding an unnecessary complexity, composite key type and should treat it within the app. Don’t do this!

I advise using the Surrogate Key, which is the column (ID) used to identify each row in the table of unique form. Usually INT is used as a data type for ID, but if there is an external integration of data, it is good to use GUID. And the registration field will only be a property of the registry and, if necessary, I would create an index for it.

There are cases where you can use Sequences, as in Oracle for example for PK’s. Worth reading about sequences as well.

Surrogate key

  • Would the use of a Surrogate Key fit the second alternative of the question? And in this case, it would be more appropriate to reference the ID in other tables, instead of the registration field?

  • Yes, that’s exactly it. That’s the way I would do it.

  • I get it. I think it would not be feasible for my application, because the tables that will point to employees table will be imported, so I have to reference the matricula field even, which already come pre-closed. But thank you so much for the suspicions, helped a lot and clarified a lot.

1

I recommend the primary key field to always be a number, even if you order a varchar you will get a different result. With values "1,2,3,4,11,21". 

em Inteiro: 1,2,3,4,11,21

em varchar: 1,11,2,21,3,4

In addition the queries become faster because the numerical index is better optimized.

And as his friend said:

Do you control that number? It will never be changed? There is not the smallest chance of the same object in the database one day need to have another unique identification? Is it short enough? It does not give margin for mistakes? Will she always be unique? She really identifies what this is object?

If these questions are answered then use your primary key.

Browser other questions tagged database modeling primary-key índices

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