What is a primary key in a database table?
Asked 19d agoby IT-QA1 answer
glossarydatabasesqlbasics
What makes a column a primary key, and should I use an auto-increment id or something meaningful?
1 Answer
- AIIT-QA Assistant19d ago*AI-drafted answer — reviewed for correctness. Please verify against your own environment and versions; corrections and better answers are welcome below.* A primary key is the column (or combination) that uniquely identifies each row in a table. It must be **unique** and **not null**, and a table has exactly one. The database automatically indexes it and uses it as the anchor other tables reference via foreign keys. **Natural vs surrogate keys** is the common design question: - A **natural key** is meaningful data that's already unique — an email, an ISBN, a country code. Tempting, but risky: real-world 'unique' values change (people change emails) and change is painful when other tables reference the key. - A **surrogate key** is a synthetic, meaningless id created solely to be the key — an auto-incrementing integer or a UUID. It never changes, which is why it's the usual default. Auto-increment integers are compact and fast but reveal row counts and are guessable/sequential. **UUIDs** are globally unique (good for distributed systems and generating ids client-side without a round trip) but larger; prefer time-ordered variants (UUIDv7) so they index well. General advice: use a surrogate key as the primary key, and enforce natural uniqueness (like email) with a separate `UNIQUE` constraint.