Question:
A simple key is associated with a single value, or field, in the record. A composite key corresponds to the combination of two or more keys, and may be necessary to eliminate ambiguity, forming a unique identifier. ( Wikipedia )
Let's assume, just as an example, that I'm going to implement a blog using a simple primary key . The records would look like this:
Tabela posts:
| id | post |
-------------------
| 1 | "Um post" |
| 2 | "Outro post" |
Tabela comentarios:
| id | post_id | comentario |
---------------------------------
| 1 | 1 | "Meu comentário" |
| 2 | 1 | "Meu comentário" |
| 3 | 1 | "Meu comentário" |
| 4 | 2 | "Meu comentário" |
| 5 | 2 | "Meu comentário" |
| 6 | 2 | "Meu comentário" |
That is: comments would have unique primary keys (column id ) against all comments, even from other posts .
If I used composite primary key the comments would have unique id 's only with respect to your post (column post_id ):
Tabela comentarios:
| post_id | id | comentario |
---------------------------------
| 1 | 1 | "Meu comentário" |
| 1 | 2 | "Meu comentário" |
| 1 | 3 | "Meu comentário" |
| 2 | 1 | "Meu comentário" |
| 2 | 2 | "Meu comentário" |
| 2 | 3 | "Meu comentário" |
The same principle applies to other situations, such as an invoice and its items, for example.
In what situations is it recommended to use the composite primary key? And when is it better to use single primary key?
Answer:
In your case, I believe that your comentário field must have its own id and the post_id must be used as a foreign key, as in the case of invoices, since in these cases you are talking about a 1xN relationship (a comment belongs to one, and only one post, the post has 0 or many comments).
The use of composite primary key can be done when you have two fields that together will always be unique for that table, for example:
In a certain airport, flights depart every day, the number of flights is always the same for a certain time and a certain destination, for example, the flight to Guarulhos at 8:00 pm is number 1212. Regardless of the day , flight 1212 will always be with the destination to Guarulhos and always at 20:00, so you cannot use voo as a primary key, not even data , as numerous flights depart on the same day. In this case you could use the voo and data fields as primary keys, as they are unique together, as you will never have the same flight twice in the same day.
| voo | data | outros campos...
--------------------------------------
| 1212 | 14/05/2014 |
| 1234 | 14/05/2014 |
| 2345 | 14/05/2014 |
| 1212 | 15/05/2014 |
| 1234 | 15/05/2014 |
| 2345 | 15/05/2014 |
You could also assign an id to each record and use a unique primary key, however this depends on your model.
Transcribing everything said in the comments:
I venture to say that for all cases where the situation resembles the dummy table of flights, you can choose either to use two columns as a composite primary key or to create an id and have a simple primary key.
Differences:
-
By choosing to use two columns as the primary key you save a field in your table, and you are already adding a constraint to your table ensuring data consistency. The disadvantage in this case is having to use two fields in every operation you do that needs to get a single record, like select, update, delete, etc. Another disadvantage is that for each relational table you make, you will have an extra field, so the first advantage I mentioned ends up not offsetting this disadvantage, since a relational table usually has many more records than the tables it relates to.
-
Choosing to use the id makes it easier to make relationships with other tables, as you will only use one field always. The downside is that you will have one more field in your table. Another point that is neither a disadvantage nor an advantage is that you will have to put the constraints aside, indicating which fields are unique, it is worth remembering that it is possible to say that two fields together are unique, similar to a composite primary key.
In most cases, the advantages and disadvantages of each are almost negligible, so choose the option that you are most comfortable with.
I could only imagine a case in which one option would be better than the other, it would be if your model has many NxN relationships, as the number of fields would increase considerably, even more so if instead of two fields you have numerous fields as a key primary, for each table you will have a much higher amount of fields and data.