A surrogate key is an application-meaningless primary key used only to identify stored data internally. It avoids coupling identity to mutable business data: changing a username need not update foreign keys throughout related tables. section 1.2.3
Prefer a dedicated surrogate primary-key column when:
- A natural key can change during the database’s lifetime, making updates to referencing foreign keys costly. section 1.2.3 section 5.2.3
- No suitable candidate key exists, or a composite natural key would make maintenance, ad hoc queries, and schema evolution difficult. section 5.2.3
- The identifier should remain an internal system value rather than something application users see or use. section 1.2.3 section 5.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)
);
A surrogate-key schema gives each table its own generated ID and uses that ID for foreign-key references, as USERS and BILLINGDETAILS demonstrate. section 1.2.3
