SQL
Used to establish and identify relations between tables.
Each record within a table can be uniquely identified by combination of one more field in a table.
Help to enforce integrity and identify relationships.
Different Types of Keys:
- Super key
- Candidate key
- Primary key
- Alternate key
- Composite key
- Foreign key
Super key:
- An attribute or set of attributes that are required to identify a tuple in a relation.
- A Super Key may contain additional attributes that are not required for unique identification.
Unique Key
RED_IDSuper Key
{SID, RED_ID}{RED_ID, EMAIL}
| SID | RED_ID | NAME | BRANCH | |
|---|---|---|---|---|
| 1 | 10001 | John | CSE | john@email.com |
| 2 | 10002 | William | IT | william@gmail.com |
| 3 | 10003 | John Doe | ECE | john123@gmail.com |
| 4 | 10004 | Luna | CSE | luna@gmail.com |
Primary Key:
- Primary key is a candidate key chosen by the database administrator for unique identification.
- No Two rows can have the primary key same values.
- The primary key have primary key value
- Value in the primary key can not be modified.
| SID | RED_ID | NAME | BRANCH | |
|---|---|---|---|---|
| 1 | 10001 | John | CSE | john@email.com |
| 2 | 10002 | William | IT | william@gmail.com |
| 3 | 10003 | John Doe | ECE | john123@gmail.com |
| 4 | 10004 | Luna | CSE | luna@gmail.com |
Example:
If we choose SID as PRIMARY KEY then RED_ID AND EMAIL will become ALTERNATE KEYForiegn Keys:
- It is an attribute in a table which is used to define its relationship with another table.
- Using foreign keys helps in maintaining data integrity for tables in relation.
Example:
| BRANCH_CODE | BRANCH_NAME |
|---|---|
| CS | COMPUTER SCIENCE |
| IT | INFORMATION TECHNOLOGY |
| ECE | ELECTRONICS AND COMMUNICATION |
Note:
STUDENT - BRANCH TABLE RELATIONSHIP.Composite Keys:
Any key with more than one attribute is called a Composite key.
Example
In the above example (SID, RED_ID),(RED_ID, EMAIL),(EMAIL,SID),(SID,RED_ID,EMAIL)
etc all are composite keys.
etc all are composite keys.
Compound Keys:
If a composite key has at-least one attribute which is a foreign key then it is called a compound key.
Example
In the above example if we have a composite key (RED_ID, BRANCH_CODE)
then it will be known as a compound key because the BRANCH attribute is a FOREIGN KEY.
then it will be known as a compound key because the BRANCH attribute is a FOREIGN KEY.
