Home computickets Should you make a composite primary key?

Should you make a composite primary key?




I am studying this question , there is such a phrase in reply

Composite primary keys typically arise
when mapping from legacy databases
when the database key is comprised of
several columns.

Do I understand correctly that legacy is almost synonymous with “obsolete”?
If so, is it worth making composite keys when developing a new application and a new database? Or just add another column, which in fact will do nothing, because all the work with the database is usually done by frameworks?

Answer 1, authority 100%

Answer 2, authority 92%

There is a somewhat outdated approach in database design, sometimes mistakenly called “academic”, when the primary key of a relationship is chosen as one of the natural keys of the relationship. Typically, this key is the name of the entry.

In your question, this is exactly the approach that is used. But this approach is outdated for many reasons. First, the primary key of a record is what is used to reference a record both in the database itself and outside of it. It is advisable to keep it as small as possible – after all, most natural keys are strings.

The second reason is that many things that at first glance look like natural keys are actually not. For example, among people there are complete namesakes (and even those born on the same day). A store can have two products with the same name in different departments …

The third reason is that the natural key can also change, which also leads to problems. So, for a person, the natural key could be the number of the passport or birth certificate – but these documents can be replaced when they reach a certain age or in case of loss.

Therefore, it is good practice to introduce a surrogate key – this is a new field that has nothing to do with the subject area. Typically, its type is an auto-incrementing 32-bit integer, less often a 64-bit integer. It could also be a UUID.

As a rule, there is no point in compound keys when such a field is present.

However, sometimes compound keys are useful. These are the cases:

  1. Link tables for many-to-many relationships. Usually such tables are created and managed by ORM libraries themselves – but sometimes you have to create them separately. In such tables, the natural key is the entire record – and there is no reason to create a separate surrogate key.

  2. Some child subobjects that do not make sense apart from the parent. If we are making a system for testing students, then sometimes it makes sense to refer to the answer to the question as the 5th answer to the 147th question – and not to the 3423rd answer at all. And sometimes vice versa.

  3. Using relations in the database for additional integrity check. There is a so-called domain-key normal form, in which any data constraints are implemented in foreign key format. In this case, it sometimes makes sense to include additional fields in the primary key.

Answer 3, authority 96%

I guess the translation is meaningful: “Composite primary keys usually occur when matching against existing database tables when the table key consists of multiple columns.”
legacy here is almost synonymous with the word “existing”.
When developing a new table, in theory, you should not create redundancy of information with a new key field if the existing combination of foreign keys (or other fields) in it is already a unique key.
But in practice, searching by an additional field of a unique key can be faster or subjectively easier for the developer and thus make sense to achieve the desired result 🙂

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions