Someone with a good heart who could give me a coherent explanation of what they are and why compound keys should be used in a single table, a table without the table's own PK, but with two (2) PKs that are typical of other tables . I go to that site since no teacher could exemplify the logical need to use and, on the internet, I only find examples of little content. Thanks and regards.
From what you describe, it seems to me that you are talking about a many-to-many, Many-to-Many relationship . For a record to be unique, it must have some way of identifying it. How do we know that the "pepe martinez" record in the people table is unique? because a property that identifies it is sought, for example in that case we could add an identification field as an identity document "DI" that would be the key of that table:
Although the names and surnames are repeated, for the model they are two different ones and their key differs (its primary key is ID ), which is actually fine because many people have the same name as others, so in this case the model is valid .
Now suppose that in this data model people can have cars, in fact they can have many cars, we have a one-to-many relationship (an entity can be related many times to another), to facilitate we will leave the table with two different names:
And we will add the cars entity, taking into account that a car has color, model and year, we will leave how to identify a serial number:
The problem now is, how is the relationship indicated above reflected in the tables (a person has many cars)? If we see the data in the people table, it does not tell us that Pepe is related to a car, just the table cars with respect to people. The answer is that we could leave the car identifier in the people table:
Ah beautiful! Pepe is related to car 444 (Ford fiesta black) and María to car 555 (Fiat Kwid verder). Serial_Auto is a foreign key since that column stores the primary key of the record it is related to. You will see that many times by convention the field is added fk_ at the beginning, leaving fk_serial_auto or fk_auto_serial, whichever sounds better, even sometimes they only leave the name of the field, fk_serial.
But... what if we want Pepe to also own the green Fiat car? Or the opposite for Maria? Well, we could say that it's good to repeat the information in the table for Pepe and add the other id:
Okay, here we have two problems, the first is that we are violating the restriction of the primary key ID, there cannot be two records with the same value of the ID column in the person table, if we change the id of the second Pepe, it is no longer Pepe himself... If we talk about María it would be the same case. The second problem is that we are repeating the information, something that is sought to be eliminated in relational databases, for that there are normalization techniques that allow us to create models that have the least number of repeated data (but that is another story).
So how do we solve this problem? Well, again, we could say that since I can't place the Car key in People, let's do it the other way around, in Cars let's place the Person ID... So if Fiat owns to Pepe would be:
Good (note that María became the owner of Kwid), but if the green car is also related to Pepe, we would have the same problem, since we repeat the Serial (which violates the primary key) and we repeat information:
Well, if the primary key cannot be placed in any table, how can this relationship be stored? First notice that the relationship we are trying to reflect is many to many, because a car can have many owners and a person can own many cars (both statements indicate many , hence the name of the relationship type). So to resolve this conflict, what must be done is to create a separate table that stores only the primary keys of both tables in the relationship:
Great, car 444 is owned by person 222, if we want car 444 to be owned by person 333 we just add another record:
Anytime you need to know who owns which car you just have to go to this table (which can be called Cars_Personas) and see the foreign key information, because Serial_Car and ID_Person are foreign keys in this table Cars_Personas. In fact, nothing happens if we see this table as People_Cars, if it makes more sense to see it that way, then it is worth:
The reason why they tell you that a table has to have two primary keys from other tables as a composite key is that in the People_Car table it also has to have its own way of identifying each record (each relationship between people and cars) in a way univocally, if in this table People_Car we place only the ID_Person field as primary, we cannot have more than one relationship between a person and a car, the following would violate the primary key restriction:
Here the database manager would say that the record with the ID 222 is already found when trying to add the record with the serial_auto 555. If the Serial_Auto field is placed as a key, then it is already violating that restriction because 444 is repeated for the record with person 333.
The solution is to indicate that in this table the way to uniquely identify a record is with both columns (a compound key), ID_Persona and Serial_Auto, together they prevent the records from being repeated, that is, we can add many people (that the value of ID_Persona can be repeated) and that we can add many cars (Serial_Auto value can be repeated) but the same combination of ID_Person and Serial_Car cannot be repeated, so this would be fine under this context (composite key):
But the following is wrong:
When trying to repeat the relationship of Person 222 with Car 555, the database engine would indicate an error since the primary (composite) key of that table is repeated.
Short answer, because in many-to-many relationships, in order to store them correctly, a new entity is created that has the keys of the two entities to be related and the primary key of that new entity will be a compound key between the foreign keys of the related entities. .