Concept of Keys
In a database, Keys are unique identifiers that ensure data integrity and establish relationships between tables. Without keys, it would be nearly impossible to distinguish one row from another or link related information across the system.
Common Types of Keys
| Key Type | Description |
|---|---|
| Super Key | Any set of one or more columns that can uniquely identify a row in a table. |
| Candidate Key | A “minimal” super key. It has no redundant attributes. A table can have multiple candidate keys. |
| Primary Key | The one candidate key chosen by the DBA to uniquely identify every row. It cannot be NULL. |
| Foreign Key | A column that creates a link between two tables. It refers to the Primary Key of another table. |
| Composite Key | A primary key consisting of two or more columns combined to create uniqueness. |
Difference between Candidate Key and Primary Key
| Feature | Candidate Key | Primary Key |
|---|---|---|
| Quantity | There can be multiple in a table. | There can be only one per table. |
| Selection | It is a “minimal” Super Key capable of uniqueness. | It is the specific Candidate Key chosen by the DBA. |
| Null Values | Can sometimes allow a NULL value (depending on the DB). | Never allows NULL values. |
| Purpose | Potential candidates for identification. | The official, unique identifier for all rows. |