Do I need to create a primary key with auto increment?

You want to know if it's best to use a natural key or surrogate .

  

Just leave the registration field as the primary key.

This is a natural key. Do you control this number? Will he never be changed? Is not there any chance of the same object in the database one day needing to have another unique ID? Is she short enough? Does it give rise to errors? Will it always be unique? Does it really identify what this object is?

Most data in the world does not meet all these criteria, so a substitute key is chosen. If you meet all this, you can use it without problems. But be careful, a lot of people think that it answers and one day discovers that it does not answer.

  

Leave the registration field with a unique index 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 independent of the number that the user handles. I can not say that it's the best in your case. If you can do well the previous item then you do not have to do this, you're just wasting space and performance.

  

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

It does not seem to me to be any advantage.

Contrary to what many people think there is always a primary key on 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. The data is always sorted into the database by the primary key, it is an index. Secondary indices usually refer to the primary index.

1) Theoretically you would have no problem doing so, it depends on how your app will handle this PK. You will have additional code fragments to validate if the PK is unique, valid, etc. But it would not be well liked by most Dev's.

2) Indices are to tunnar the basis for searches rather than for managing Primary Keys.

3) By doing so you would be adding an unnecessary complexity, type compound key and should treat this within the app. Do not do it!

I advise you to use the Surrogate Key , which is the column (ID) used to identify each row in the unique shape table. Usually INT is used as data type for ID, but if there is external data integration, it is good to use GUIDs. And the enrollment field will be just a record property 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. It pays to read about sequences as well.

Surrogate key

I recommend the primary key field to always be a number, even though ordering a varchar will have a different result. With the 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 to queries getting faster because the numeric index is better optimized.

And as the friend said:

  

Do you control this number? Will he never be changed? There is not the slightest   chance of the same object in the database one day needing to have another   unique identification? Is she short enough? It leaves no room for   mistakes Will it always be unique? It really identifies what this is   object?

If these questions are answered then use your primary key.