Foreign key can become primary key?

First, answers to your questions:

Q: Can this happen without creating inconsistency?

A: In the current structure this template allows data inconsistency (a telefone without Cliente , for example).

A: Yes, but in very specific cases . If you have an identifier in two tables of the same value, for all practical purposes you are identifying the same entity; in this case, why not just have a table?

Let's first analyze your data model. If I understood correctly, this would be a representation:

Accordingtothismodel,acontactisidentifiedbyatelephoneandisusedtouniquelyidentifyaCustomer.

Problemswiththistemplate:

• Whathappensifacustomerhas2ormorephones?
• Whathappensifacustomerhasemail,butnotaphone?
• Whathappensif2customerssharethesamephonenumber?

Yourtemplateneedstobeabstractenoughtocoverasmanytrivialsituationsaspossible.Anotheraspectistheprimarykeydefinition:avalue(or,alternatively,values)thatisnotrepeatedandthatuniquelyidentifiesarecordinitstable.Neitherphonenornamecoversthisspecification.

Step1:CreatingaIDfieldforuniquerecordid.

Create a field with a unique initialization (a numeric autocontroller or GUID , for example) that will serve as the unique identifier for the record. Create a field in your contato , Cliente_ID table, and use it as a foreign key.

In this way you allow homonyms (which will have different IDs) and repeat phones without violating your model.

But it's still not good enough.

Step 2: Contact modeling by type

Create a table, Contact_Type, and store there any contact you need. For example:

Contato_Tipo
ID  Descricao
1   Telefone
2   Fax
3   Email

Modify your Contato table to, instead of storing a value of each type, now reference the ID field of the Contato_Tipo table via the foreign key Contato_Tipo_ID . Store a single value there, in the Valor column.

Let's simulate a Customer, Goku. Their telephone number is 555-4433, and their email is [email protected]. The values in the tables would be as follows:

Cliente
ID    Nome
1     Goku

Contato
ID    Cliente_ID    Contato_Tipo_ID   Valor
1     1             1                 555-4433
2     1             3                 [email protected]

This model has great advantages over the previous one. It allows 0-N values of 0-N types for each client; and when the need to add another type of contact appears (StackOverflow profile link, for example), you do not need to change its structure - just add another record to the Contato_Tipo table.

No problem the key of a table is also a foreign key. These concepts are independent of each other.

The key and primary key concept serves to restrict the values between rows in a table. Two rows in a table can not have the same values in the columns of a key.

On the other hand, the foreign key restricts the values of a column to only values that exist in the referenced table.

In your example, foreign key constraint means that we can only insert customers who have had their phone and fax inserted into the contact table. In addition, the primary key constraint means that we can not have two clients sharing the same phone.

The colleagues have already explained the issue perfectly, but I would like to add it with a practical example that shows how this situation (foreign primary key E) is possible as well as possible.

Think of a Product table, which receives products from various companies. It may suffer from the fact that different products can have the same id (each company registered their products with their ids). Updates, for example, would mess things up. To solve, we can put the companyID column in the Product, make it a primary key AND a foreingkey for the company table. Product.CompanyID < - > Company.id

CREATE TABLE Produto (
id INTEGER NOT NULL,
descricao VARCHAR (255) NOT NULL,
idDaEmpresa INTEGER NOT NULL REFERENCES Empresa(id),
CONSTRAINT pkProduto PRIMARY KEY (id, idDaEmpresa)
)

I agree with the colleagues who have confirmed above that there is no inconsistency. It is perfectly possible and logical - and I say more, it should be put! If in doubt always apply the foreign key.

Let's look at the example of a user entity that specializes in physical person or legal entity. The primary key of the user entity must be the same for the specializations. I can not put another key and neither work with key composition, because it would allow the duplication of records with the same key of the table users. Ex: if the table key users were cpf and there was key composed in the specializations, such as an integer and self-incremental id and the cpf itself, there could be error-free population (by the bank) of the same cpf in other tuples for both specializations, that would be a logical mistake. Because there is only one cpf for each user.