CODEWITHSUNDEEP
sqlsql-servercomposite-primary-key
Sayan analytics
Sayan analytics

Reputation: 21

composite primary key with practical approach

I just need to understand the concept behind the composite primary key. I have googled about it, understood that it is a combination of more than one column of a table.But my questions is, what is the practical approach of this key over any data? when i should use this concept? can you show me any practical usage of this key on excel or SQL server?

It may be a weird type of question for any sql expert. I apologize for this kind of idiotic question. If anybody feels it is an idiot question, please forgive me.

Upvotes: 2

Views: 53

Answers (2)

dfundako
dfundako

Reputation: 8314

Let's say I have a table of cars. It includes the model and make of the cars. I do not want to insert the same exact car into my table, but there are cars that will have the same make and cars that will have the same model (assume both Ford and Toyota make a car called the 'BlergWagon').

I could enforce uniqueness of make/model with a composite key that includes both values. A unique key on just make would not allow me to add more than 1 Toyota and a unique key on just model would not allow me to enter more than 1 BlergWagon.

Another example would be grades, terms, years, students, and classes. I could enforce uniqueness for a student in a class and a specific semester in a specific year so that my table does not have 2 dupe records that show the same class in the same semester in the same year with the same student.

Another part of your post is about primary key, which I'll assume means you are talking about a clustered index. Clustered index enforces order of the table. So you could throw this onto an identity column to order the table and add a unique, nonclustered index to enforce uniqueness on your other columns.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

A typical use-case for a composite primary key is a junction/association table. Consider orders and products. One order could have many products. One product could be in many orders. The orderProducts table could be defined as:

create table orderProducts (
    orderId int not null references orders(orderId),
    productId int not null references products(productId),
    quantity int,
    . . .
);

It makes sense to declare (orderId, productId) as a composite primary key. This would impose the constraint that any given order has any given product only once.

That said, I would normally use a synthetic key (orderProductId) and simply declare the combination as unique.

The benefit of a composite primary key as that it enforces the uniques (which could also be done with a uniqueness constraint). It also wastes no space that would be needed for an additional key.

There are downsides to composite primary keys as compared to identity keys:

  • Identity keys keep track of the order of inserts.
  • Identity keys are typically only 4 bytes.
  • Foreign key references consist of only one column.
  • By default, SQL Server clusters on primary keys. This imposes an ordering and can result in fragmentation (although that is doubtful for this example).

Upvotes: 1

Related Questions