SQL KEYS
SQL KEYS are single or multiple attributes used to get data from the table according to the requirement or condition. They can also be used to set up relationships amongst various tables. There are several types of SQL KEYS, which are:
- Super key
- Candidate key
- Primary key
- Alternate key
- Composite key
- Unique key
- Foreign key
1. SUPER KEY
A SUPER KEY is a combination of one or multiple columns in a table in the database, which help in the unique identification of each row in the table. It is a group of one or several keys.
Example:
| EmpSSN | EmpNum | EmpName |
| 1254654 | TAE03 | Harry |
| 2165465 | TAE04 | Ron |
| 2154864 | TAE05 | Dobby |
In the above example, EmpSSN and EmpNum are SUPER KEYS because they help in identifying each row in the table uniquely.
2. CANDIDATE KEY
CANDIDATE KEY is also a set of columns or attributes that help identify each row in the table separately. A CANDIDATE KEY can be defined as a SUPER KEY having no matching attributes. It can be demonstrated as a sub-set of SUPER KEY. Several candidate keys can be there in a table.
Example:
| StuID | Roll | FirstName | LastName | |
| 01 | 42 | Harry | Kane | harrykane@gmail.com |
| 02 | 43 | Ron | Wiesley | ronwiesley@yahooo.co.in |
| 03 | 44 | Dobby | Wright | dobbywright@hotmail.com |
StuID, Roll and Email are CANDIDATE KEYS in the above table because they help to identify each row uniquely.
3. PRIMARY KEY
PRIMARY KEY is an attribute or a group of attributes that help in identifying individual rows distinctly. There cannot be the exact value of the PRIMARY KEY more than once in the table. A PRIMARY KEY can be expressed as a sub-set of a CANDIDATE KEY. There cannot be Multiple PRIMARY KEYS in a table.
Properties of a PRIMARY KEY:
- There cannot be duplicate values of PRIMARY KEY in the table.
- PRIMARY KEY cannot contain null values.
- The value of a PRIMARY KEY should not be changed with time.
- Each individual row in the table should contain a PRIMARY KEY.
Example:
| StuID | Roll | FirstName | LastName | |
| 01 | 42 | Harry | Kane | harrykane@gmail.com |
| 02 | 43 | Ron | Wiesley | ronwiesley@yahooo.co.in |
| 03 | 44 | Dobby | Wright | dobbywright@hotmail.com |
StuID is the primary key in the above example since it can uniquely identify each record in the table.
4. ALTERNATE KEY
ALTERNATE KEY helps in identifying the records in the table distinctly. There can be several columns in a table that can identify individual rows in the table separately. Out of those attributes, only one attribute is chosen as the PRIMARY KEY. The rest of the attributes become ALTERNATE KEYS.
Example:
| StuID | Roll | FirstName | LastName | |
| 01 | 42 | Harry | Kane | harrykane@gmail.com |
| 02 | 43 | Ron | Wiesley | ronwiesley@yahooo.co.in |
| 03 | 44 | Dobby | Wright | dobbywright@hotmail.com |
In the above example, Roll and Email are ALTERNATE KEYS.
The following representation will help understand CANDIDATE KEY, PRIMARY KEY, and ALTERNATE KEY in a better way.
5. COMPOSITE KEY
COMPOSITE KEY is a merger of multiple columns that help in identifying each row distinctly. This distinctness is guaranteed only when the columns are combined. When the columns are taken individually, it does not promise distinctiveness. A PRIMARY KEY which is made of multiple attributes, is defined as a COMPOSITE KEY.
Example:
| OrderNo | ProductID | ProductName | Quantity |
| A001 | 5624185 | LCD | 1 |
| A001 | 3216546 | Printer | 2 |
| A001 | 3516527 | Mouse | 3 |
| A002 | 9816846 | Keypad | 1 |
| A003 | 7160354 | USB | 5 |
In the above example, OrderNo and ProductID combined to form the COMPOSITE KEY. They individually cannot identify each row in the table uniquely, but when they are combined, they can identify each record in the table uniquely.
6. UNIQUE KEY
UNIQUE KEY can also identify each row in a table uniquely like a PRIMARY KEY. But, unlike a PRIMARY KEY, a UNIQUE KEY can have only a single null value. There can be several UNIQUE KEYS in a table.
Example:
Let’s consider a Student table having the following columns.
In the above columns, CityID is the UNIQUE KEY. Suppose, if a student leaves the city and goes abroad for studies, then that student’s CityID will not be there. In that case, that attribute will become null and null values are allowed in UNIQUE KEY.
7. FOREIGN KEY
A FOREIGN KEY in a table is an attribute that establishes a relationship between two tables. A FOREIGN KEY of one table references the PRIMARY KEY of another table, establishing the relation between the two tables. A FOREIGN KEY can accept multiple null and duplicate values.
Example:
Let’s consider the following two tables, the Students table, and the Order tables.
The first table is the Students table.
| StuID | FName | LName | City |
| 1 | Harry | Kane | Kolkata |
| 2 | Ron | Wiesley | Noida |
| 3 | Dobby | Wright | Mumbai |
The second table is the Order table.
| OrderID | OrderNo | StuID |
| 1 | 65498545 | 3 |
| 2 | 46546854 | 2 |
| 3 | 21654698 | 3 |
| 4 | 65165415 | 1 |
The StuID in the Students table is the PRIMARY KEY, and the StuID in the Order table is the FOREIGN KEY.
These are the essential keys in SQL that should be given importance while creating or dealing with databases.