In SQLite, a primary key uniquely identifies each row in a table and cannot have null values, while a unique key also ensures uniqueness but can contain null values. You would use a primary key when you want to enforce a strict unique constraint on a row, and a unique key when you need unique values but allow for nulls.
In depth
The primary key is essential for the integrity of a database, serving as the main identifier for a record. It is implicitly indexed, ensuring that lookups are efficient. A table can only have one primary key, which is defined at the time of table creation and can be composed of a single column or a combination of multiple columns. In contrast, a unique key constraint enforces the uniqueness of the values in one or more columns but allows for nulls, meaning you can have multiple records with null values but only one record with a specific non-null value. This makes unique keys suitable for fields that must remain unique yet where having an undefined state is permissible. You may choose a unique key over a primary key if your application logic allows for multiple entries with null values and you still need to enforce uniqueness for the non-null values.
Example scenario
In a user management system, you might have a 'users' table where the 'user_id' serves as the primary key since each user must have a unique identifier. However, if you also want to enforce that email addresses are unique for login purposes but allow users to not provide an email during registration, you would use a unique key on the 'email' column. This setup allows for flexibility in user data while maintaining data integrity.
Common mistakes
A common mistake is to try to use a unique key as a primary key, leading to confusion about nullability. Since primary keys cannot be null, one might incorrectly assume that a unique key constrains all values similarly. Another error is neglecting to index columns that will frequently be queried with unique constraints, resulting in performance hits. Developers may also mistakenly create multiple unique constraints when a single one is sufficient, complicating the schema without clear benefits.
Follow-up questions
- Can you explain how indexing works with primary and unique keys?
- How would you handle a scenario where a primary key and a unique key conflict?
- What impact does choosing a composite primary key have on database design?
- Can you discuss the performance implications of primary keys vs unique keys?
Want all 2,000 questions in one PDF?
- Short answers, in-depth explanations, common mistakes and follow-ups
- 89 topics, from Beginner to Architect, with a clickable contents list
- 226 extra questions that are not on this website
- Free Bonus Pack: 30-day study plan, behavioral questions, salary scripts
Includes 226 extra questions that are not on this site, plus a free Bonus Pack.
Get both books for $9.99 (save $2.99):
The material stays free to read online. This is the organised, offline, print-ready PDF edition. Secure payment by PayPal; your personal download link is emailed after payment is confirmed. Digital product, no shipping.