A natural key is a primary-key candidate with business meaning: an attribute or combination of attributes that is unique because of domain semantics. Users can recognize and use it outside the database, such as an item number or tax identifier. section 5.2.3
Evaluate a natural key against the requirements of a durable primary key:
- Require uniqueness, immutability, and non-null values; few business attributes satisfy all three reliably. section 5.2.3
- Expect changes to be expensive because updating a primary key can require updating every referencing foreign key. section 5.2.3 section 1.2.3
- Treat combinations of attributes as composite natural keys when uniqueness depends on several columns, while recognizing the resulting maintenance complexity. section 5.2.3
- Declare alternative candidate keys as database unique keys when their values are unique, even if they are not immutable. section 5.2.3
Username versus a surrogate identifier
Using USERNAME as the USERS primary key makes a name change propagate through foreign-key values in BILLINGDETAILS. A schema using generated ID columns instead separates database identity from user-facing business data. section 1.2.3
CREATE TABLE USERS (
ID BIGINT NOT NULL PRIMARY KEY,
USERNAME VARCHAR(15) NOT NULL UNIQUE,
. . .
);
CREATE TABLE BILLINGDETAILS (
ID BIGINT NOT NULL PRIMARY KEY,
ACCOUNT VARCHAR(15) NOT NULL,
BANKNAME VARCHAR(255) NOT NULL,
USER_ID BIGINT NOT NULL,
FOREIGN KEY (USER_ID) REFERENCES USERS(ID)
);
Compare a surrogate ID used for database identity with USERNAME retained as a unique business attribute and USER_ID used for the foreign-key relationship. section 1.2.3
