Are there any Ecto-specific reasons not to use natural keys as primary keys?

[toraritte]

I know that the natural keys vs. surrogate keys debate has been raging for decades, but I’m more interested whether it is detrimental to use natural keys as primary keys with Ecto?

It seems to be discouraged; at least the Custom Primary Keys guide has been completely removed since Phoenix 1.3.0-rc.3, and even this guide says that

it’s not the natural path Ecto wants to take. Allowing Ecto to use an auto-incremented integer is definitely the right way to go for new applications.

but there are no specifics however.

Thanks in advance for any insights and for sharing your experience!

[OvermindDL1]

I personally like custom primary keys for lookups based on outside data that is not linked to, but integers for links between tables.

[hauleth]

I almost always use UUIDs instead of autoincrementing integers. But in general my reason to use integer/UUID primary keys is that these are easier to work with than strings.

[OvermindDL1]

My ‘natural’ keys are often composite keys, not strings. Like a foreign link is a primary key for example, combined with a removed_at field for a composite primary key. In almost all cases the removed_at field is combined with 1 or more other fields to make a composite primary key.

[NobbZ]

I do not really trust in natural keys, often enough they are only unique on first thought, not on further inspection.

[OvermindDL1]

Never ran into an instance where they aren’t? What are you referencing?

[peerreynders]

@toraritte

I know that the natural keys vs. surrogate keys debate has been raging for decades

Choosing a Primary Key: Natural or Surrogate? (2002):

The advantage of natural keys is that they exist already, you don’t need to introduce a new “unnatural” value to your data schema. However, the disadvantage of natural keys is that because they have business meaning they are effectively coupled to your business: you may need to rework your key when your business requirements change.

For example, if your users decide to make CustomerNumber alphanumeric instead of numeric then in addition to updating the schema for the Customer table (which is unavoidable) you would have to change every single table where CustomerNumber is used as a foreign key.

[christhekeele]

I’ve been bitten by this before. You might argue that it’s because the domain was poorly designed or natural keys poorly chosen, but in my experience a well-designed schema takes into account that business logic may change, including assumptions about uniqueness forming natural keys.

Much easier to slap a uuid primary key on everything, and unique indexes that are trivial to roll back without migrating all foreign key references, on what you suspect would make a good natural key.

One example that comes to mind is a JIRA-style project_id + database_driven_project_auto_incrementing_int natural key for issues within a project. Bulk migrating issues between projects becomes tricky to do correctly, transactionally, in the database.

[toraritte]

So I guess there are no Ecto-specific reasons for not using natural keys. Thank you for all your input, because I re-considered using them, and will just stick with IDs/UUIDs.

Have a great day!