Detailed explanation
Primary keys, unique constraints, and foreign keys protect different aspects of data integrity. A primary key identifies each row, a unique constraint prevents duplicate values in a candidate identifier, and a foreign key ensures that a relationship points to an existing referenced value. For example, users.id can identify a user, users.email can prevent duplicate email addresses, and users.dept_id can link that user to a department. These constraints enforce rules even when data arrives from different applications or scripts.
A primary key combines uniqueness with NOT NULL: every row must have a distinct, non-null key. A table can have only one primary key constraint, but that key may contain multiple columns; this is called a composite primary key. A primary key does not automatically generate values unless an identity, sequence, or auto-increment mechanism is configured. A table can have multiple unique constraints, including composite ones. Unlike a primary key, a unique constraint does not inherently make its columns NOT NULL. However, NULL handling varies: PostgreSQL and MySQL normally allow multiple NULLs in a single-column unique constraint, while SQL Server normally allows only one.
A foreign key requires each non-null referencing value to match a referenced key, typically a primary key or unique constraint. It can reference another table or the same table, as in an employee's manager_id referencing employees.id. Foreign key values may repeat, allowing many users to belong to one department. They may also be NULL unless NOT NULL is specified. When referenced rows are updated or deleted, configured actions such as CASCADE, SET NULL, or rejection determine what happens to dependent rows.
Databases typically use unique indexes to enforce primary-key and unique constraints. Foreign-key enforcement checks relationships during relevant writes, but an index on the referencing columns is not automatically created in every database. In an interview, explain each constraint's purpose, compare uniqueness and nullability, and qualify database-specific behaviour rather than claiming that every unique constraint allows exactly one NULL.
Key points to remember
- PRIMARY KEY means unique and NOT NULL; one constraint per table, potentially spanning multiple columns.
- UNIQUE prevents duplicate keys; multiple unique constraints are allowed, and NULL behaviour depends on the database.
- FOREIGN KEY enforces referential integrity against a referenced key, including within the same table.
- Foreign key values can repeat and can be NULL unless additional constraints prohibit this.
- In the example, departments must exist with an eligible referenced key on id before creating the users foreign key.
Common mistakes candidates make
- Saying a unique constraint always allows exactly one NULL instead of checking database-specific behaviour.
- Confusing one primary key per table with one column per primary key, or assuming primary keys automatically increment.
- Assuming a foreign key must be unique, must be non-null, or must reference a different table.
Likely follow-up questions
Can a column be both a primary key and a foreign key?
Yes, for example, user_profiles.user_id can be its primary key and also reference users.id. This permits at most one profile per user and prevents profiles from referencing nonexistent users.
What happens if we insert an invalid dept_id or delete a referenced department?
With the foreign key enforced, inserting a non-null dept_id that has no matching department fails. Deleting a referenced department is normally rejected unless an action such as ON DELETE CASCADE or ON DELETE SET NULL is configured.
How do composite keys work?
A composite key uses a combination of columns: PRIMARY KEY (student_id, course_id) makes each enrolment pair unique, although either value may repeat separately. A foreign key referencing that composite key uses corresponding columns to match the referenced combination.
Interview tip: Start with each constraint's purpose, use the users example to compare them, and explicitly mention the database-specific NULL rule for UNIQUE.