CODEWITHSUNDEEP
sqldatabasedatabase-design
k80sg
k80sg

Reputation: 2473

SQL Server + Composite key or Unique Id

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

Upvotes: 5

Views: 2005

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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.

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176896

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

Upvotes: 3

Related Questions