A natural key uses meaningful domain attributes to identify a database record. It can seem convenient, but composite natural keys become fragile when attributes are imprecise, numerous, or likely to change. section 12.1.1 section 12.1.2
Treat natural keys cautiously because they can create several production problems:
- Avoid floating-point attributes: rounding makes exact equality unreliable, even when the programming language supports comparison. section 12.1.1 section 12.1.2
- Expect multi-field natural keys to complicate queries, consume extra memory in relationships, and become difficult to maintain when fields change. section 12.1.2
- Prefer a unique surrogate key when the domain contains many records or its identifying attributes may change; possible forms include integers, UUIDs, or hashes. section 12.1.2
Meteor records
A meteor table initially uses (FLOAT, FLOAT, TIMESTAMP) as a composite natural primary key. Replacing it with an integer meteor identifier avoids floating-point equality and reduces coupling to changeable domain fields. The domain and database models can remain separate through conversion functions. section 12.1.1 section 12.1.2
ghci> 0.1 + 0.1 == 0.2 True ghci> 0.1 + 0.2 == 0.3 False
The floating-point comparison demonstrates why numeric domain attributes are unsafe components of natural or primary keys. section 12.1.2
