Should i use composite primary keys

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.

What you call a synthetic key is generally called a surroagate key. My own answer is about linking two tables which themselves have surrogate keys - there's absolutely no point in creating a further surrogate for this - see comments! You correct the value in the primary key column, and the DBMS automatically propogates the change to all referencing foreign keys.

Determining benefits of choosing a table key includes abstract theory and practical implementation details. In Microsoft SQL Server, a nonclustered index is, to ignore a lot of options, another table whose columns are the columns used in the index, plus the clustered index key. The computer finds the data you want much more easily than in a table with all the columns. Your fine example of a relationship between students and courses ends up with two indexes; one mainly provides courses of one student at a time, the other finds students of one course - neither need be unique but it's handy, — Robert Carnegie.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Does ES6 make JavaScript frameworks obsolete? Podcast Do polyglots have an edge when it comes to mastering programming Featured on Meta. 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. However there's one good reason to no do that in MS SQL Server at least : if you want to manage the physical sorting of your data in the underlying storage system.

The primary key clustered determines the physical sorting order. If you do it on an identity column, the physical sorting order is basically the insert order. However, this may not be the best, especially if you always query the table the same way. On very large tables, getting the right physical sorting order makes queries a lot faster. For example you may want the clustered index on a composite of two columns. Best practices are helpful at best, but blinding at worst.

Going against a best practice isn't a sin. Just be sure you know what kind of trade-off you are making. Database engines can be very complicated things. Other "undefined-undefined" queries related to "Should I use composite primary keys or not?

UseSelector returns me undefined Keep the button focused when click on input: Material-Ui How we can retrieve all data by all users in firebase? How to make react to wait until api data is retrieved when use fetch How do I pause the animation in React Spring?

How to register event with useEffect hooks? Fabric JS - Applying multiple filters on a single object causes lag, how to increase performance? Can't use array functions like. Using the Chakra-UI useDisclosure hook on both a parent and child component. Related Questions. Simulate backspace key with java. Java 8 Supplier with arguments in the constructor Is there a way to redirect to another Action class without using on struts.

How cancel the execution of a SwingWorker? Why String class has copy constructor?