When is the use of composite primary key recommended?

Asked

Viewed 22,703 times

15

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

Let’s suppose, just as an example, that I will implement a blog with the use of single 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: the comments would have primary keys (column id) unique in relation to all comments, even from others posts.

If I used composite primary key the comments would have id'only with respect to your post (spine 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 a tax note and its items, for example.

In what situations is the use of composite primary key recommended? And when it is best to use simple primary key?

sql database modeling primary-key

  • What is the difference in the table comentários the first and the second example? The way it is posted, they are identical, only something is missing. The only difference is that in the second example you do not show the table of posts. The way it is I’d say the answer is to use any one.

  • @bigown Pay attention to comments id’s. In the second example they just don’t repeat themselves in relation to the column post_id. In the first example they do not repeat I relation to any other comment, even those of other posts.

  • 1

    Got it, your question is about using a general ID or an ID that is actually a specific post item.

  • ID vs composite key: how? when? why?

3 answers

8


In your case I believe your field comentário must have its own id and must have the post_id being used as a foreign key, as in the case of tax bills, since in these cases you are talking about a 1xN relationship (a comment belongs to one, and only one post, already 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 to that table, for example:

At a given airport flights leave 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 the time of 20:00 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, because in the same day leave numerous flights. In that case you could use the fields voo and data as primary keys, because together they are unique, since they will never have the same flight twice in a single 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 single primary key, however this depends on your modeling.

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

Differences:

  • Choosing to use two columns as a primary key saves you a field in your table, and also you are already adding one Constraint its table ensuring the consistency of the data. The downside in this case is having to use two fields in every operation you do that needs to take a single record, like select, update, delete, etc. Another downside is that for every relational table you do, you will have one more field, So the first advantage I mentioned does not compensate for this disadvantage, since a relational table usually has much more records than the tables it relates.

  • Choosing to use the id makes it easier to make relationships with other tables, as you will only use one field every time. The disadvantage is that you will have one more field in your table. Another point that is not disadvantage or advantage is that you will have to put the constraints the 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 composite primary key.

In most cases, the advantages and disadvantages of each are almost disregardable, so choose the option you get the most at ease.

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

  • and if you programmatically took those 2 keys and converted them into a single id with type: 112214052014, it would not be better in terms of research?

  • 2

    The summary of all this is that primary key, by default should be simple, should be a general ID for the table (there are cases that should be universal for the whole base or even something unique even beyond the base)a composite key should only be used if there is real justified need for it.

  • 1

    @Jorgeb. in terms of efficiency: I have no idea; in terms of ease of operation: doubtful, as you would simply be duplicating the data and would have to make an extra account to fetch the id. In fact the idea of keeping these fields as the primary key instead of making a sequential id is by a Constraint to validate the data, as this would ensure that there is no inconsistency in the data.

  • @bigown then in this case above would put an id instead of flight key, date?

  • Still the same, one says to do with id the other with the primary key pair, where we stay? :)

  • 1

    @Jorgeb. It is always complicated to speculate without specific real cases, a model cannot be looked at by one or two tables, without seeing the general context, but taking a risk anyway, I would say yes. I I prefer always an ID. Even facilitates future model changes. Using the composite key may even be a form of optimization, it would be something similar to denormalizing. Maybe I should give my answer :) But I don’t know if it’s justified. If I remember how to base it, I put.

  • 1

    @Jorgeb. depends on your need, the two ways are technically correct, ie you will not have inconsistency of the data. I particularly always choose to put sequential ids, there was never the case of me need of composite primary keys. If you want to ensure consistency in the case of the flight table you can simply put the two fields as unique, meanwhile a unique compound, and let the sequential id be the primary key.

  • I’m saying that because I have a case I was gonna ask yesterday, but I ended up not having time, and it fits right in here. I have a case where I have 4 composite keys to identify a table row. And I thought, should I have or use an ID? I think it would be interesting to clarify this in the answers to this question. ID vs Composite key how? when? because?

  • @Math but what are the advantages and disadvantages of using id?

  • 1

    @Jorgeb. would say that the advantage of not using the id is that you would have one less field in your table, however I don’t know how far that is advantageous. The advantage of using id is that it is easier when you will relate to another table because once you have only one field as a primary key you will avoid doing ANDs in queries and other operations. In short: the difference is minimal, or I do not know other (des)advantages, rs.. for me are those. I mean, I don’t know of cases that you need do it one way or another, it goes to the taste of the client.

  • Composite key has its uses and is sometimes better than creating a (third) id. A common example are relational tables where we have a tAxB table relating ta and tb tables and we have only one A for each B and the ids of A and B are unique

  • @jean what’s the point of creating a relational table for a 1xN relation? Relational tables are useful only in Nxn relationships.

  • @Math I think he was just talking about a many-to-many relationship.

  • @Andrey temos apenas um A para cada B gave me to understand that is 1xN

  • @I didn’t see much of an advantage between doing what you said and creating a id for this table and a Constraint ensuring the unity of the reference(s)).

  • @Math You’re right, now I understand what he said =)

  • @Math I speak of a generic example Nxm and not of the example posted by OP which is 1 to N and which looks better using a single simple id per comment

Show 12 more comments

4

Answering the initial question of whether or not to use composite PK. From experience I’ve learned taking a beating that it’s 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
  • Update/delete on Where is easier and faster (imagine you have to use 10 fields on Where
  • Easier to view in other tables
  • Inner Join and left Join are easier and faster to make

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

I hope I’ve helped.

  • "Updating / delete on Where is easier and faster (imagine you have to use 10 fields on Where" This if you already have Id previously, otherwise it’s the same.

4

Composite key should be used if and only if you want to ensure 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 integrity using unique compound indices and I have never had to resort to composite PK. Because in the end this adds complexity when consulting and a manipulate a record.

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

Sincerely, I recommend never use.

Browser other questions tagged sql database modeling primary-key

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