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:

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.