Why composite primary key

Viewed 41k times. My question is based on this article Note the part about composite primary keys: Bad Practice No. Improve this question. This isn't a "good" or "bad" practice. Every design decision must serve a purpose; if you can explain to yourself and others why you need a composite PK, you're good to go. Conversely, if you can explain why you don't need it, you're good to go as well. The article you link to does a very poor job explaining, in my view.

Read what John Eisbrenner has to say about them here! Add a comment. Active Oldest Votes. That way, you can't get: a duplicate of student and course combination a course can only have the same student enrolled once, and a student can only enroll in the same course one time only you also have a ready made search KEY on course per student - AKA a covering index , it is trivial to find courses without students and students who are taking no courses!

An example from my own work! Improve this answer. I have a similar situation as the student courses example you mentioned, but with the composite primary key with 2 columns, I also have 3 more columns whose data I need to update based upon the combination of composite keys.

How do I do that? I searched but couldn't find proper example. Thanks — Vipulw. Vipulw - sounds like a new question to me. You can refer back here - that might be helpful?

Robert Carnegie Robert Carnegie 4 4 silver badges 10 10 bronze badges. I especially appreciate the observation that depending on customer data as a key, even known unique customer data here, SSN , breaks down if that data ever needs to be corrected. This is an example of making use of a surrogate key. A composite key is a specific type of primary key which uses the contents of two or more fields from a table to create a unique value.

Consider the number of times certain footballers scored a goal during a tournament. In the above example a single field would not create a set of unique values, eg there are two Aberdeen players, and there are three players with the squad number 8. However, if you combine the Team and Squad number fields, a primary key is created, allowing each player to be uniquely identified.

This is an example of a composite key. A compound key is similar to a composite key in that two or more fields are needed to create a unique value. However, a compound key is created when two or more primary keys from different tables are present as foreign keys within an entity.

The best practice is to have some column or columns that uniquely identify a row. But in some tables a single column is not enough by itself to uniquely identify a row. SQL and the relational model allows a composite primary key. It is a good practice is some cases. Or, another way of looking at it is that it's not a bad practice in all cases. Some people have the opinion that every table should have an integer column that automatically generates unique values, and that should serve as the primary key.

Some people also claim that this primary key column should always be called id. But those are conventions , not necessarily best practices. Conventions have some benefit, because it simplifies certain decisions. But conventions are also restrictive. You may have an order with multiple payments because some people purchase on layaway , or else they have multiple sources of payment two credit cards, for instance , or two different people want to pay for a share of the order I frequently go to a restaurant with a friend, and we each pay for our own meal, so the staff process half of the order on each of our credit cards.

This is also related to the concept of identifying relationship. If it's definitional that a payment exists only because an order exist, then make the order part of the primary key. Note the LineItems table also lacks its own auto-increment, single-column primary key. A many-to-many table is a classic example of a good use of a composite primary key. This question is dangerously close to asking for opinions, which can generate religious wars. As someone who is highly biased toward having auto-increasing integer primary keys in my tables called something like TablenameId , not Id , there is one situation where it is optional.

One very important reason is for reference purposes. In a relational database, any entity could -- in theory -- be referenced by another entity via foreign key relationships. For foreign keys, you definitely want one column to uniquely define a row. Otherwise, you have to deal with multiple columns in different tables that align with each other.

This is possible, but cumbersome. The table you are referring to is not an "entity" table it is a "junction" table. It is a relational database construct for handling many-to-many relationships.

Because it doesn't really represent an entity, it should not have foreign key relationships. Hence, a composite primary key is reasonable. There are some situations, such as when you are concerned about database size, where leaving out an artificial primary key is even desirable.

It will make queries, joins, indexes and other constraints easy to manage. R Programming. React Native. Python Design Patterns. Python Pillow. Python Turtle. Verbal Ability. Interview Questions. Company Questions. Artificial Intelligence.

Cloud Computing.