Database Key


Today one new label is added. "Database". There are set of articles planned in this category. Today is the first one. 

Most of us know, in RDBMS, database table has primary key and foreign key. Any idea about other types of key? Below tables describes all database keys and relations among them. 



Primary
Unique
Surrogate
Foreign
Composite
Simple
1
Primary
1
1
May be
May be
May be
May be
2
Unique
May be
1
May be
May be
May be
May be
3
Surrogate
May be
1
1
May be
0
1
4
Foreign
0
May be
0
1
May be
May be
5
Composite
May be
May be
0
May be
1
0
6
Simple
May be
May be
0
May be
0
1
7
Super
0
1
0
0
May be
May be
8
Minimal super
May be
1
0
0
May be
May be
9
Tirival Super
May be
1
0
May be
1
0
10
Candidate
May be
1
May be
May be
May be
May be
11
Alternate
0
1
1
May be
May be
May be
12
Natural
May be
1
0
May be
1


Super Minaml super Tirival Super Candidate Alternate Natural
1 Primary May be May be 0 1 0 May be
2 Unique 1 May be May be 1 May be May be
3 Surrogate 0 0 0 May be May be 0
4 Foreign May be May be 0 0 0 0
5 Composite May be May be 1 1 May be 0
6 Simple 0 May be 0 May be May be May be
7 Super 1 May be 1 0 0
8 Minimal super 1 1 0 1 May be May be
9 Tirival Super 1 0 1 1 May be 0
10 Candidate 1 1 1 1 May be May be
11 Alternate 1 1 0 1 1 May be
12 Natural 0 1 May be 1



The above table should be read, row wise

For example 1st row: 

Is primary key is unique? 1 = Yes
Is it surrogate? The surrogate key can become primary key. However the primary key is not always surrogate. So answer "May be"
and so on. Here 1 = Yes and 0 = No

Here are some key points about database keys
  • The primary key cannot be NULL
  • The unique key can be NULL
  • The primary key is always unique
  • All unique keys are candidate key to become primary key
  • Primary key can be one of the candidate key or it can be surrogate key
  • The candidate key, who is not primary key is alternate key. 
  • If surrogate key is primary key, then all candidate keys are alternate keys. 
  • Simple key and composite key are mutually exclusive. 
  • The compound key should have at least two simple keys
  • The composite key is extension of compound key
  • Natural key and domain key are synonyms.
  • Composite key, compound key, aggregate key and concatenated key. All are in a way same. 
  • Minimal super key and candidate key are in a way same. 
  • Trivial super key means all columns.   
Stay tune to this blog "Express YourSelf !" for more interesting articles. 

0 comments:

Post a Comment