Postgres supports composite compound primary keys: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS

It also supports composite compound foreign keys, so you might not even need that id column: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK

I just read this in the docs: > PRIMARY KEY enforces the same data constraints as a combination of UNIQUE and NOT NULL. However, identifying a set of columns as the primary key also provides metadata about the design of the schema, since a primary key implies that other tables can rely on this set of columns as a unique identifier for rows.

I'd like to keep the id column because its data will be immutable whereas the primary key as I described it (let's call it the natural key from now on) could change its data.

https://en.m.wikipedia.org/wiki/Surrogate_key

I like surrogate keys better, because sometimes you want to write logic that touches multiple tables, or doesn't care about the table, and that gets a lot harder if every table can have its own type of primary key.

Thanks. I think I'll stick with surrogate primary key for the most part. I'm curious about using a compound primary key if the members of that key are guaranteed to be immutable although I take your point about consistency, @U08JKUHA9.

The problem is that even if a compound (or even non-compound) primary key is immutable but actually represents something about the data, you can have a whole lot of other problems. Sure, the data may be for internal use today, but will it be tomorrow? What if the user wants his data to be deleted from DB, and that primary key is also eligible for deletion, but you can't actually do it because it'll break constraints over the code? And what if the "guaranteed to be immutable" become not that guaranteed tomorrow because of some extreme edge-case? Lots of questions...

So you would avoid a compound foreign key altogther?

I would

people make a lot of bad assumptions about what's immutable and what's unique, especially over time

a lot of shops just have a rule, every row gets a synthetic key

I'd only trust something that's specifically designed to be unique across the domain and has no other meaning. that way the meaning can't change and invalidate your assumptions since it's not tied to anything else

and storage is cheap these days ¯\(ツ)/¯

plus, you can share an arbitrary surrogate key without sharing what could amount to private information (PII or otherwise) which is a handy property to have in a key

"storage is expensive" is usually the first reason given for using surrogate keys in the first place! 😛

I've never heard that argument but I guess it must be a thing

I suppose indexes of compound primary keys and using them for foreign keys would take up more space

so that makes sense

Big indices used to be a concern. Maybe they still are. I've never really used them

I've only ever worked with surrogate primary keys, partly because most interaction was through ORMs, and partly because it was the received wisdom that that's what you do

but yeah, people used to stuff table data into indices to optimise query performance, but the trade-off was that you could fill up your disc space

people still do. on postgres, at least, you can do index-only scans with covering indexes

That's good to know. I don't think I've seen anyone optimise a query in at least 10 years

@UVDD67FFX weird, I do this all the time. In fact, most problems I solved in the last few years were about poor queries, usually made in ORMs

I've had jobs where I do that a lot and jobs where I don't. the different kinds of problems to solve are not evenly distributed

If you decide to go down the route of using a surrogate key to identify a record in a table you should also create a UNIQUE constraint against some of the columns (what you refer to as the natural key above) to prevent duplicates creeping in. An automatically-generated surrogate key on its own will not prevent duplicates.

it's quite common in cassandra schemas for a bunch of reasons

it's mostly an artifact of how it works internally and access patterns you might need