Primary key in associative table(Many for many)

Asked

Viewed 2,419 times

3

Good night.

I have the following question in relation N to N. Foreign keys become a primary key composed of the associative table. Correct?

Does this guarantee that there is no repetition between the tuples? Because I think even if they make up the primary key there can be repetition, so I suppose to add another primary key field to ensure integrity (func_projectoID). Or my thinking is wrong?

Thank you for reading. Greetings.Imagem exemplificando

sql table normalization
  • 1

    This is exactly what is done in a real system. Only composite key is not always used. In theory, in modeling, you can present this idea that you will pass without errors. But in implementation, what you see is a pk func_projectoID, a column fk func_id and other fk project_id. Anyway, your thinking is correct.

  • Thank you very much for the clarification!

  • @Andrewribeiro Write an answer, please.

  • @Leonardo +1, but please write a more meaningful title. Your question is excellent. Database modeling is perhaps the most important part of a software project. I’m still surprised we don’t have so many questions about that. We need to document the questions and answers well, because they will surely serve people who had the same question.

2 answers

2


In theory, your thinking is totally correct. In practice, only partially.

What you see in practice (which is somewhat disappointing) are developers ignoring the creation of the key composed of implementation issues.

But with regard to the idea of the primary key preceding the other two columns it is very common to observe.

Would something like this:

pk func_projeto_id

fk func_id

fk func_projeto

To conclude, adding a primary key to the associative table is a cool and very common idea to find in most software.

2

I have the following question in relationship N to N. The keys foreign become a primary key composed of the table associative. Correct?

Correct. N-to-N relationships should generate a table of their own that will have a 1-to-N relationship with each of them. The primary key of this table will be composed by the primary keys of the tables that generated this new.

This assures me that there is no repetition between the tuples?

Yes there will be no repetition between tuples, because the key will be composed of the primary keys of each table, and by definition the keys are unique.

Below is an image of a slide that my teacher used to teach. I think it illustrates well.

inserir a descrição da imagem aqui

NOTE : My answer is academic and not practical. I have not yet had the opportunity to work on a real system.

  • Thanks. Thanks for the answer. I was trying to find a way to break the integrity and I did. Assuming a doctor makes appointments with a particular patient. The ID of the doctor and the patient will have repetitions in the BD because of the various consultations throughout the year, which will cause inconsistency in the database. In such cases, that there will be several interactions of the same elements it is necessary to base the primary key with one more attribute besides the Foreign Keys.

  • Correct me if I’m wrong. But if you want to save query history. You will need an extra attribute. In this case the date. What you said is the definition of associative entity. You could not even save in the bank, because it is the same key. It would overwrite. Soon there would be no repetition of tuples containing (id_medico,id_patient).

  • Exactly, that’s what I observed. That is why I thought it best to put a primary key for the associative table and not the Foreign Keys, just in case of repetition. Vlw

Browser other questions tagged sql table normalization

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