🔑 Various Types of Keys
- What is a primary key?
A primary key is a field (or a set of fields) that uniquely identifies each record in a table.
It cannot contain NULL values, and each table can have only one primary key.- What is a unique key?
A unique key ensures that all values in a column are different from one another.
Unlike a primary key, it can allow NULL values (usually one or more, depending on the database).- What is a candidate key?
A candidate key is any column (or combination of columns) that can uniquely identify a row.
A table can have multiple candidate keys, but only one is selected as the primary key.- What is a foreign key?
A foreign key is a field in one table that points to the primary key in another table.
It is used to create relationships between tables and maintain referential integrity.- What is a composite key?
A composite key is a primary or candidate key made up of two or more columns.
The values in these columns together must be unique, even if the individual values are not.- What are the differences between these keys?
| Key Type | Uniqueness | NULL Allowed | Main Purpose | Can Be Composite |
|---|---|---|---|---|
| Primary Key | Yes | No | Uniquely identifies each record | Yes |
| Unique Key | Yes | Yes (usually one) | Enforces uniqueness on a column | Yes |
| Candidate Key | Yes | Sometimes | A possible choice for primary key | Yes |
| Foreign Key | No (references another key) | Yes | Links to primary key in another table | Yes |
| Composite Key | Yes | Sometimes | Ensures uniqueness using multiple columns | Always |