A primary key in SQL is a unique identifier for a record in a table. It ensures that each entry is distinct and helps maintain data integrity by preventing duplicate records.
In depth
A primary key is a column or a set of columns in a table that uniquely identifies each row. This means no two rows can have the same values in those columns, ensuring data integrity and efficiency in data retrieval. Primary keys are critical for establishing relationships between tables in a relational database, as foreign keys in related tables must reference the corresponding primary key. Additionally, they often create automatic indexes, improving query performance when searching or joining tables.
It's important to choose primary keys wisely. They should be stable and not change frequently to avoid complications in related tables. Composite primary keys, which consist of more than one column, can be used in scenarios where a single column does not uniquely identify a record. Care must be taken to ensure that all columns in the composite key are included in any operations to avoid issues with data consistency.
Example scenario
In a customer database for an e-commerce platform, the 'customer_id' column serves as the primary key for the 'customers' table. This ensures that each customer is uniquely identified and prevents duplication — for example, two customers cannot have the same 'customer_id'. When orders are placed, the 'customer_id' is used as a foreign key in the 'orders' table to associate each order with the correct customer, thus maintaining a clear relationship between customers and their orders.
Common mistakes
One common mistake is using non-unique columns, like a name or email, as a primary key, which can lead to data integrity issues if duplicates occur. Another mistake is to overlook the importance of choosing a stable key; using a value that changes, like a phone number, can complicate relationships in the database. Developers may also forget to account for composite keys, leading to incomplete data relationships which could affect query results.
Follow-up questions
- What are some alternatives to primary keys?
- Can you explain what a foreign key is?
- How would you handle updates to a primary key value?
- What happens if you try to insert a duplicate primary key?
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.