SQL Server + Composite key or Unique Id

Question

I am fairly new to database design, for many to many relationship, what is the differences and implications of creating a composite key and a unique id for e.g.

Country table

CountryID
CountryName

Language table

LanguageID
LangugageName

Many to Many table - using composite:

CountryID  Pkey
LanguageID Pkey

OR

Using unique Id:

AutoID Pkey
CountryID 
LanguageID

check the updated answer that will give you clear idea .. – Pranay Rana Nov 01 '12 at 08:15 — Pranay Rana, Nov 01 '12 at 08:15

Pranay Rana · Accepted Answer · 2012-11-01T08:14:53.287

Composite Key :

A composite key is a combination of more than one column to identify a unique row in a table. composite key can be a primary key .

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a database table.

so its all depend on your requirement

in first design

Many to Many Table:
Using composite:
CountryID  Pkey
LanguageID Pkey

if you use this desing than CountryID and LanguageID is composite primary key.i.e here

data of the table will be

CountryId  LaguageID
  1           1 //valid
  1           2 //valid
  1           3 //valid
  1           1//not valid data as its form composite primary key

and in second design

Using Unique Id:
AutoID Pkey
CountryID 
LanguageID

AutoID is become primary key so this will allow data lke thsi

AutoId    CountryId  LaguageID
 1          1           1 //valid
 2          1           2 //valid
 3          1           3 //valid
 4          1           1 //valid as AutoID is primary key
 1          2           3 // invalid as AutoID is prinary key

hope this presentation help you to understand difference

Hi, thanks for the input, actually what I would like to find out more is the pros and cons and should I be using one over the other. — k80sg, Nov 01 '12 at 08:17
Isn't the unique id in the second case in the OP's question a [**Surrogate Key**](http://en.wikipedia.org/wiki/Surrogate_key). It will be better if you add this to your answer. — Mahmoud Gamal, Nov 01 '12 at 08:23

score 0 · Answer 2 · edited May 23 '17 at 12:03

what is the differences and implications of creating a composite key and a unique id for e.g.

You'll need to create a "natural" key on {CountryID, LanguageID} to avoid duplicated connections in any case. The only question is whether you'll also need a "surrogate" key on {AutoID}?

Reasons for a surrogate key:

There are child tables that reference this junction table (and you'd like to keep their FKs slim or prevent ON CASCADE UPDATE propagation).
You are using an ORM that likes simple PKs.

Unless some of these reasons apply, use only the natural key.

BTW, under a DBMS that supports clustering, a natural key like this is usually a good candidate for a clustering key. If you cluster the table, every other index (such as the one underneath the surrogate key) has extra overhead (compared to an index in a heap-based table) since it needs to keep the copy of clustering key data and can cause a double-lookup.

See also: A column as primary key or two foreign keys as primary key.

SQL Server + Composite key or Unique Id

2 Answers2

Linked