1. Primary Key
2. Composite Key
3. Candidate Key
4. Super Key
5. Foreign Key
Primary Key:
- Primary key is the one which uniquely identifies the records in a table.
EMP ID | EMP NAME | SALARY |
1 | JOHN | 5000 |
2 | JACKSON | 6000 |
3 | JOHN | 12000 |
- Here “EMP ID” is the PRIMARY KEY.
Composite Key:
- Composite Key is the collection of columns which uniquely identifies the records.
STUDENT ID | COURSE ID | STUDENT NAME | COURSE NAME | DATE OF COMPLETION |
11AD12001 | 101 | SAMRAT | COLD FUSION | 15/01/2011 |
11AD12002 | 102 | HARISH | JAVA | 16/07/2011 |
11AD12001 | 102 | SAMRAT | JAVA | 16/07/2011 |
- Here Using Student Id / Course Id, we can’t retrieve a record. Hence to get unique data, we need the combination of Student Id & Course Id.
- Here (Student Id & Course Id) combined is the COMPOSITE KEY.
Candidate Key:
- Candidate Key is the collection of columns which are eligible for PRIMARY KEY.
VEHICLE NO | VEHICLE NAME | ENGINE NO | PRICE | MODEL |
AP09AZ1001 | PULSAR | 1234-234-567 | 65000 | 150CC |
AP12BR0007 | CBZ | 1023-433-876 | 67000 | LZ |
AP22RZ9999 | Royal Enfield | 4444-444-444 | 150000 | Thunderbird |
- Here Vehicle No, Engine No, are eligible for PRIMARY KEY.
- Hence Vehicle No, Engine No are CANDIDATE KEY.
- If Vehicle No is PRIMARY KEY, then Engine no is ALTERNATE KEY.
Super Key:
- Different Set of attributes which uniquely identifies a record in a relation.
DEPT NO | DEPT NAME | LOCATION |
101 | CSE | HYDERABAD |
102 | ECE | HYDERABAD |
103 | IT | HYDERABAD |
- Here SUPER KEYS are :
DEPT NO + DEPT NAME + LOCATION = SUPER KEY 1
DEPT NO + DEPT NAME = SUPER KEY 2
DEPT NO + LOCATION = SUPER KEY 3
DEPT NAME + LOCATION = SUPER KEY 4
DEPT NO = SUPER KEY 5
DEPT NAME = SUPER KEY 6
-
-
- CANDIDATE KEY = MIN ( SUPER KEY ) { DEPT NO, DEPT NAME }
- If DEPT NO is PRIMARY KEY, Then DEPT NAME is ALTERNATE KEY.
FOREIGN KEY:
- It is used to establish the relationship between 2 relations (table).
EMP NO | EMP NAME | SALARY | DEPT NO |
1 | John | 30000 | 101 |
2 | Sam | 25000 | 102 |
3 | James | 33000 | 101 |
DEPT NO | DEPT NAME | LOCATION |
101 | SALES | HYDERABAD |
102 | SUPPORT | HYDERABAD |
- Here Foreign Key is “DEPT NO”.
- If the relationship is 1 to MANY, FOREIGN KEY must be added to many side tables.
- If the relationship is 1 to 1, FOREIGN KEY can be added to any table.
- If the relationship is Many to Many, FOREIGN KEY cannot be added to any of the tables.
- Hence RDBMS doesn’t support MANY to MANY relationships.
- In RDBMS, MANY to MANY can be split into one to many relations.
Supplier No | Supplier Name |
101 | John |
102 | Jackson |
Customer No | Customer Name |
301 | James |
302 | Bill |
Supplier No | Customer No |
101 | 301 |
102 | 301 |
102 | 302 |
101 | 302 |
