Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
For most database schemas, name the primary key id in the users table and name columns that reference it user_id. Use user_id as a primary key when the row is a one-to-one extension of a user, such as a user’s settings. The name is a convention; the more consequential decisions are what uniquely identifies the row, which constraints enforce that, and whether the key should be an integer, UUID, or another value.
What the names mean
In users.id, id identifies a row in the users table. In posts.user_id, user_id identifies the user associated with that post. A common convention is therefore:
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
Grokking Relational Database Design | $45.49 | Buy on Amazon |
| 2 |
|
Learning SQL: Generate, Manipulate, and Retrieve Data | $34.65 | Buy on Amazon |
| 3 |
|
Practical SQL, 2nd Edition: A Beginner's Guide to Storytelling with Data | $19.99 | Buy on Amazon |
| 4 |
|
SQL Database Query Programmer T-Shirt | $16.99 | Buy on Amazon |
- Use
idfor an entity table’s own primary key. - Use
<entity>_idfor a foreign key to that entity.
For example:
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE posts (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
title TEXT NOT NULL
);
SELECT users.id, posts.id
FROM posts
JOIN users ON users.id = posts.user_id;The naming makes each column’s role apparent. Prefixing every primary key with its table name—users.user_id, posts.post_id—is also valid if it is your team’s consistent standard. Conversely, calling every key simply id can require table qualification or aliases in joins. Neither naming style changes the underlying key behavior.
When user_id should be the primary key
If a table can contain at most one row per user and that row has no independent identity, the user’s key can identify it directly:
#1 Best Overall
CREATE TABLE user_settings (
user_id BIGINT PRIMARY KEY REFERENCES users(id),
timezone TEXT NOT NULL,
marketing_opt_in BOOLEAN NOT NULL
);Here, user_id does two jobs: it references a real user and, through the primary-key constraint, prevents a second settings row for the same user. The same pattern can suit a profile or preferences table.
If other records need to refer to the settings or profile as an independent entity, give it its own id and make user_id unique instead:
CREATE TABLE user_profiles (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL UNIQUE REFERENCES users(id),
display_name TEXT
);The UNIQUE constraint is what makes the relationship one-to-one. A foreign key alone only requires the referenced user to exist; it does not prevent multiple child rows from pointing to that user.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Does every table need an id column?
No. A table needs a dependable way to distinguish its rows, but that key need not be a single generated id. A primary key can contain multiple columns, and a stable, authoritative natural key can be appropriate. PostgreSQL describes primary keys as unique and non-null and supports composite keys; see its constraint documentation.
Many-to-many relationships
A join table is often identified by the pair of records it connects:
CREATE TABLE user_roles (
user_id BIGINT NOT NULL REFERENCES users(id),
role_id BIGINT NOT NULL REFERENCES roles(id),
PRIMARY KEY (user_id, role_id)
);This rules out assigning the same role to the same user twice. Add a separate id only if the relationship row itself needs an independent identifier. If you do, keep UNIQUE (user_id, role_id); a surrogate key does not enforce that business rule by itself.
Natural keys
A compact, stable, mandatory, authoritative value—such as a country’s ISO code—can be a suitable primary key. Many values that look like natural keys are less reliable: email addresses, usernames and phone numbers can change or have normalization and collation issues. A common approach is a surrogate key plus a separate uniqueness rule, such as email TEXT NOT NULL UNIQUE. A generated key does not make email unique unless the database constraint says so.
Recommended Free Tools
Choose the key type separately from its name
id versus user_id is a naming choice. Integer versus UUID is an identity-design choice.
- Integer or bigint: Often a practical internal key: compact, straightforward to inspect, and efficient to carry in foreign keys. A single database or coordinated allocator can generate values. Sequential values may reveal approximate insertion order and are guessable if exposed, and allocation across independent writers requires planning.
- UUID: Useful when records must be generated by independent systems or before database insertion, or when cross-database uniqueness matters. UUIDs take more space than integer keys and can be less readable; index and insertion behavior depends on the UUID version, storage representation and database. PostgreSQL has a native
uuidtype and documents UUIDs as 128-bit identifiers: PostgreSQL UUID documentation.
Neither kind of key provides authorization. A sequential integer may be easy to enumerate, while a UUID may be harder to guess, but an application must still check whether the requester is allowed to access the requested record.
Some systems keep a compact internal key and a distinct public identifier:
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id UUID NOT NULL UNIQUE
);Use this only when the separate external identifier solves a real integration, URL or exposure requirement. It adds another value to manage and a uniqueness index. Do not add both id and user_id to users without clearly distinct roles.
Free tools Windows power users keep installed
One-click scans. No signup required.
Constraints and indexes still matter
The column name does not enforce a relationship. Use a primary-key constraint for row identity, a foreign-key constraint for referential integrity, and UNIQUE for business rules such as one profile per user or one membership per team-user pair.
Rank #4
- Database Programming design. Funny database SQL joke that makes a great gift for database administrators, programmers or computer scientists. Fun gift for database administrators, programmers and hackers who like to wear funny nerd clothes.
- Funny gift for men and women who love SQL. The perfect SQL Query top for programmers, hackers and SQL database fans who love relational databases.
- Lightweight, Classic fit, Double-needle sleeve and bottom hem
Consider indexing foreign-key columns used in joins, filtering, or deletes. PostgreSQL creates an index for a primary key, but does not automatically index the referencing columns of a foreign key; consult its constraint guidance and assess the workload. Indexing behavior and costs vary by database.
Key width can matter even though the key’s name does not. In InnoDB, primary-key values are included in secondary-index entries, so a wider key can increase index storage. MySQL documents this behavior and its primary-key rules in CREATE TABLE.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Database syntax varies
The naming recommendation is broadly portable, but value-generation syntax and key behavior differ:
Quick Recap
- PostgreSQL: For a generated integer key, use an identity column, for example
BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY. ChooseALWAYSorBY DEFAULTbased on whether explicit values must be accepted for imports or migrations. See identity and default values. - MySQL with InnoDB: A typical form is
BIGINT UNSIGNED NOT NULL AUTO_INCREMENTwith a primary-key constraint. InnoDB’s secondary-index storage makes key width worth considering. See the MySQL table-creation reference. - SQLite:
INTEGER PRIMARY KEYhas special rowid behavior. Do not addAUTOINCREMENTreflexively; it has distinct semantics. See SQLite CREATE TABLE. - SQL Server:
IDENTITYgenerates values; it does not itself declare a primary key. Define the key constraint separately. See SQL Server IDENTITY property.
Practical rules to avoid mistakes
- Use role-specific foreign-key names when a table references the same entity more than once:
sender_idandrecipient_idare clearer thanuser1_idanduser2_id. - Do not add
UNIQUE(user_id)to a posts table if a user can have many posts. Add it only for a genuine one-to-one relationship. - Do not assume auto-generated IDs have no gaps or reliably encode creation time. Store
created_atwhen you need a timestamp. - Do not treat a UUID or an obscure column name as an access-control mechanism.
- Choose one table-naming and key-naming convention for the project and apply it consistently.
Quick decision guide
- Ordinary entity such as users, posts or orders? Use
idas its primary key and name referencesuser_id,post_idororder_id. - One dependent row per user, with no independent identity? Make
user_idthe child table’s primary key and a foreign key tousers.id. - Relationship identified by a pair? Consider a composite primary key such as
(team_id, user_id). - Stable authoritative natural identifier? It may be the primary key, but preserve all required uniqueness and validation rules.
- Need keys from independent writers or a public opaque identifier? Consider a UUID, or a separate public UUID alongside an internal key; neither replaces authorization.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
