Natural primary key is one that contains a real data, not just a random value, as an integer.
Example:
// comum
CREATE TABLE pessoas (
id INTEGER PRIMARY KEY,
cpf INTEGER NOT NULL,
// outros campos
);
// chave natural
CREATE TABLE pessoas (
cpf INTEGER PRIMARY KEY,
// outros campos
);
In the case above I use the person's CPF as a key, since it does not repeat itself. Is this recommended?
Short version - No, not recommended.
Long version - I would understand your question as follows:
The benefit of having a database without process artifacts versus the maintenance cost of a natural primary key results positively?
Let's now go to a quick enumeration of the strengths of each option:
In this implementation, your data model is as close as possible to the universe represented by the scope. Crossing data from external databases is facilitated by the fact that your data is used as a natural identifier (for example, CPF or identity card.)
A data model is designed to reproduce the behavior of the data present in the universe defined by the scope. However, being an abstract model, it must provide its own mechanisms to allow this representation.
If you use a natural template, you are delegating responsibility for uniqueness . If this uniqueness is violated, your model will be negatively impacted. For example, two people with the same CPF, or a CPF that was mistakenly typed and which is already present in the database.
In situations where data is incomplete and natural identifier fields are not yet present, such a model would still allow the creation of records containing a partial representation.
I'd say it's not recommended.
How to do with foreigners? What about minors who do not yet have a social security number? In that case, they can not create an account?
Imagine that someone was creating an account on any website and by mistrust or even by malicious intent such people resolved not to use their own CPF number and picked up a CPF number through a generator. One day the real holder of the CPF number decides to create an account on the site, obviously it will be blocked because the system will inform you that there is already an account linked to that CPF. How to resolve this situation?
I'm not saying it's impossible to solve, but you'll be creating extra complexity just because you did not want to use an auto increment as the primary key and decided to use the CPF.
Is there any great reason not to let the user have two accounts? You have not allowed the child to have an account yet, and will not allow the account holder to have two accounts (one for him and one for his child, for example). Maybe he'll take the CPF from grandma, or use a sigh.
Suppose the user created an account and never logged in again, after a while he tries to sign in again, but he does not remember the password and also lost his password or changed his email. You'll need an extra plan to recover the user's password and keep exactly the same account that he created at first.
Again, I'm not saying it's impossible, but it's an extra complexity you'll have to develop, while you could just let the user create a new account.
Assuming a Sky subscription for example, if one day I want to transfer the ownership without having to cancel the current account, return the device, sign a new account and receive a new device. How to do?
Okay, you can change the PK and use triggers or whatever, but it's another extra complexity in your system.
Unless you have a great reason, the only "advantage" I've thought so far was: save a field in the table.
Not a good idea. Here are some arguments:
1 - A person can either be a natural person (CPF) or a legal entity (CNPJ).
2 - If the person does not have the document in hand, or the user that is registered does not have this data, the registration would simply not be done, or you would have to invent a temporary and valid above (if the system do CPF validation).
3 - In the future, the government can extinguish the CPF and create a single document, or maybe rename CPF to another name.
4 - I do not think a good idea of a PK depends on a data informed by the user. If you want to have only one person with a specific CPF, simply create a UNIQUE KEY.
Not a good idea and there are several reasons for this! First the rule of business can change, whether by a change in law, or because the business has changed, and in this case, you would have problems with the natural primary key. It is recommended to create the table with a generic key (ID) and if necessary, ensure that the natural key is a Unique Key. Creating a Unique Key ensures that the natural key is never repeated, but the relationship with other tables will be accomplished by the "generic" key, the ID.