SQL Server + Composite key or Unique Id

database, database-design, sql

Solution

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

Problem

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 ```

Original source

Related problems