Can foreign key become primary key?

Question

exemplifying, I have two tables: customer and contact.

The contact table contains the customer’s phone column as primary key, fax column and e-mail column.
The client table contains the customer’s phone column as primary key and foreign key and the name column.

+-------------------------------+      +--------------------------------------------+
| contato                       |      | cliente                                    |
+-------------------------------+      +--------------------------------------------+
| telefoneCliente (primary key) |      | telefoneCliente (primary key, foreign key) |
| fax                           |      | nome                                       |
| email                         |      |                                            |
+-------------------------------+      +--------------------------------------------+

First: this can happen without creating inconsistency?
Second: is there any way that the value of a primary key is the same as the value of a primary key of another table?

Answer 1

First, answers to your questions:

P: This can happen without creating inconsistency?

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

P: there is some way the value of a primary key is the same value of a primary key of another table?

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

We will initially analyze your data model. If I understood correctly, this would be a representation:

According to this model, a contact is identified by a telephone, and this is used to uniquely identify a Customer.

Problems with this model:

• What happens if a customer owns 2 or more phones?
• What happens if a customer has email, but doesn’t call?
• What happens if 2 customers share the same phone number?

Your model needs to be abstract enough to cover as many trivial situations as possible. Another aspect is the definition of the primary key: a value (or, alternatively, values) that does not repeat itself and uniquely identifies a record in its table. Neither phone nor name cover this spec.

Step 1: The creation of a field ID for exclusive identification of the registration.

Create a field specifically with single initialization (a numerical autocomperer or GUID, for example) which will serve as the unique identifier of the record. Create a field in your table contato, Cliente_ID, and use it as a foreign key.

This way you allow homonyms (who will have different Ids) and repeat phones, without violating your model.

But it’s still not good enough.

Step 2: Modeling of contacts by type

Create a table, contact type, and store all the contact types you need there. For example:

Contato_Tipo
ID  Descricao
1   Telefone
2   Fax
3   Email

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

We will simulate a Client, Goku. Your phone number is 555-4433, and your 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 one more contact type appears (Stackoverflow profile link for example), you don’t need to change its structure - just add one more record to the table Contato_Tipo.

Answer 2

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

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

On the other hand, the foreign key restricts the values in a column to only those in the table.

In your example, the foreign key restriction means that we can only insert customers who have had their phone and fax inserted into the contact table. Also, primary key restriction means we can’t have two customers sharing the same phone.

Answer 3

Colleagues have already explained the question perfectly, but I would like to add with a practical example that shows how this situation (foreign and primary key) beyond possible is very useful.

Let’s think about a Product table that receives products from several companies. It may suffer from the fact that different products may have the same id (each company has registered its products with their ids). Updates, for example, would mess everything up. To solve, we can place the idDaEmpresa column on the Product, make it a primary key and a foreingkey to the company table. Product.idDaEmpresa <--> 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)
)

Answer 4

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

Let’s take the example of a user entity that specializes in a natural or legal person. The primary key of the user entity must be the same for specializations. I can not put another key and nor work with key composition, because it would allow the duplicate records with the same key of the table users. Ex: if the key of the table users was Cpf and there was a composite key in the specializations, such as an integer and auto incrementable id and Cpf itself, there could be population without errors (by the bank) of the same Cpf in other tuples for both specializations, which would be a logical error. Because there is only one Cpf for each user.