When is the recommended use of composite primary key?

Question

When is the recommended use of composite primary key?

Navigation

#1 by (8 votes)
#2 by (4 votes)
#3 by (3 votes)

13

A simple key is associated with a single value, or field, of the record. A compound key corresponds to the combination of two or more keys, and may be necessary to eliminate ambiguity, forming a unique identifier. ( Wikipedia )

Let's suppose, as an example, that I implement a blog with the use of 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 primary keys (column id ) unique to all comments, even from other posts.

If I used compound primary key the comments would have id 's only relative to their 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 the use of the composite primary key recommended? And when is it better to use simple primary key?

sql modelagem chave-composta

asked by anonymous 15.05.2014 / 14:21

3 answers

4

Composite key should be used if and only if you want to ensure the referential integrity in your database that there will never be an equal combination of these keys for a record in the same table.

I confess that I can guarantee this integration using unique composite indices and have never had to resort to composite PKs. Because in the end this adds complexity when querying and manipulating a record.

Another point that can be taken into account here is the faster indexing if you want to for example save joins using a query in one of the compound keys or indices but this could be done with FKs as well as composite PKs.

I honestly recommend never to use .

15.05.2014 / 16:02

3

Answering the initial question about using or not using composite PK. From experience I learned by taking it hard that it is not good to use composite key.

We can cite as an example to use single PK:

Database that has identity fields (auto sequential) is easier to use
Doing update / delete no where is easier and faster (imagine you having to use 10 fields in the where
Easier to see in other tables
inner join and left join are easier and faster to do

The only reason for a composite key would be an N: N table, but in this case I prefer to do a single PK, and the constrain to validate the other fields.

I hope I have helped.

15.05.2014 / 15:04

Why is using String in a switch block more efficient than in an if-else block? PDO Drivers for SQL Server

score 8 · Accepted Answer

In your case, I believe your comentário field should have its own id and should have post_id being used as a foreign key, just like in the case of invoices, since in these cases you are talking about a relationship 1xN (a comment belongs to one, and only one post, since the post has 0 or many comments).

Using the composite primary key can be done when you have two fields that together will always be unique to that table, for example:

In a given airport, flights run every day, the number of flights is always the same for a certain time and destination, for example, the flight bound for Guarulhos at 8:00 p.m. is number 1212. Regardless of the day, flight 1212 will always be with the destination to Guarulhos and always at the time of 20:00, so you can not use voo as the primary key, not even data , since in the same day many flights depart. In this case, you could use the voo and data fields as primary keys, since they together are unique since you will never have the same flight twice in one 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 for each record and use a single primary key, however that depends on your modeling.

Transcribing everything that was said in the comments:
I venture to say that for all cases the situation resembles the dummy flight table, you can choose either by using two columns as a composite primary key or by creating an id and having a primary key simple.

Differences:

Choosing to use two columns as the primary key saves a field in your table, and you're 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 pick up a single record, such as select, update, delete, etc. Another disadvantage is that for each relational table you make, it will have one more field, so the first advantage I mentioned ends up not compensating for this disadvantage, since a relational table usually has many more records than the tables it relates to. / p>
By choosing to use the id, it is easier to make relationships with other tables, since you will only use one field at a time. The downside is that you will have one more field in your table. Another point that is neither disadvantage nor advantage is that you will have to put the constraints part, indicating which fields are unique, it is worth remembering that it is possible to say that two fields together are unique, similar to a primary key compound.

In most cases, the advantages and disadvantages of each are almost unmentionable, so choose the option that you feel most comfortable.

I could only imagine a case where one option would be better than the other would be if your model has many NxN relationships, because the number of fields would increase considerably, even more if instead of two fields you have countless fields as the primary key, for each table you will have a much larger number of fields and data.