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