Explain the difference between a primary key and a foreign key.
Explanation:
In the context of relational databases, a primary key and a foreign key are two types of constraints that help maintain the integrity and consistency of the data.
-
Primary Key: A primary key is a unique identifier for a record in a table. It ensures that each record within a table is unique and can be easily referenced. A primary key consists of one or more columns that uniquely identify each row in the table.
-
Foreign Key: A foreign key is a field (or a collection of fields) in one table that uniquely identifies a row of another table or the same table. It establishes a relationship between two tables and enforces referential integrity by ensuring that the foreign key value in one table corresponds to a primary key in another table.
Key Talking Points:
-
Primary Key:
- Uniquely identifies each record in a table.
- Must contain unique values and cannot contain NULLs.
- There can only be one primary key per table.
-
Foreign Key:
- Links two tables together.
- Can contain duplicate values and NULLs.
- There can be multiple foreign keys in a table.
NOTES:
Reference Table:
| Feature | Primary Key | Foreign Key |
|---|---|---|
| Uniqueness | Must be unique | Can have duplicates |
| NULL Values | Cannot be NULL | Can be NULL |
| Purpose | Uniquely identifies a record | Establishes a relationship between tables |
| Number Allowed | One per table | Multiple allowed per table |
Follow-Up Questions and Answers:
-
Q: Why can't a primary key contain NULL values?
- Answer: A primary key cannot contain NULL values because it must uniquely identify each record. A NULL value would indicate the absence of a value, which would violate the uniqueness constraint.
-
Q: Can a table have more than one primary key?
- Answer: No, a table can only have one primary key. However, a primary key can be composed of multiple columns, known as a composite key.
-
Q: How do foreign keys enforce referential integrity?
- Answer: Foreign keys enforce referential integrity by ensuring that the value in a foreign key field matches a value in the primary key field of the referenced table, thus maintaining consistent and valid data across tables.