Primary Keys and Identifiers: Designing for Data Integrity

Previously in this course, we discussed the transition from high-level logical models to the Logical vs Physical Schema: Designing Databases for Implementation. Now that we are preparing to move our design into actual code, we must solve the most fundamental requirement of any relational database: uniquely identifying your records.

What is a Primary Key?

In the context of data integrity, a Primary Key (PK) is a constraint that identifies every row in a table. Think of it as a unique "fingerprint" for your data. Without a PK, your database becomes a bucket of records with no reliable way to point to a specific user, subscription, or invoice.

To be a valid Primary Key, an identifier must satisfy three strict rules:

1. Uniqueness: No two rows can share the same value.
2. Non-nullability: Every row must have a value; you cannot have a record without an identity.
3. Immutability: The value should never change. If the identifier changes, you break every reference pointing to that record.

Natural vs. Surrogate Keys

When modeling our SaaS entities, you will often face the choice between two types of identifiers:

Natural Keys

A natural key is an attribute that exists in the real world and is inherently unique. Examples include an email address for a user or a social security number for an employee.

• Pros: They carry business meaning and save you from creating an extra column.
• Cons: They are risky. If a user changes their email, or if government policy changes how identifiers are formatted, your entire system breaks. In modern SaaS design, avoid natural keys as your primary identifier.

Surrogate Keys

A surrogate key is an artificial, system-generated value that has no business meaning. The most common examples are auto-incrementing integers (BIGINT) or Universally Unique Identifiers (UUID).

• Pros: They are completely decoupled from business logic. You can change a user’s name, email, or physical address without ever touching the record's identity.
• Cons: They require an extra column, and they aren't human-readable.

Implementing Identifiers in our SaaS Project

As we continue building our Designing Subscription Models: SaaS Relationship Mapping, we need to finalize the keys for our users and subscriptions tables.

For a SaaS application, I recommend using BIGINT auto-incrementing columns for internal database operations or UUID version 4 for distributed systems.

Example: Defining a User table structure

SQL
-- Using an auto-incrementing integer for a simple SaaS app
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In this example, user_id is our surrogate key. Even though email is also unique, we keep it as a separate UNIQUE constraint rather than the primary key. This allows the user to update their email address in the future without cascading changes through every table that references them.

Hands-on Exercise: Selecting Identifiers

Look at your current project draft. Identify the accounts table we discussed in our Refining the Conceptual Model: Validating Your ERD lesson.

1. Decide if you will use a BIGINT or a UUID for the account_id.
2. List three attributes in your accounts table that are unique but should not be your primary key.
3. Sketch out the column definition for the account_id based on your decision.

Common Pitfalls

• Using Composite Keys by default: While sometimes necessary, using multiple columns as a primary key (e.g., account_id + user_id) makes your foreign keys significantly more complex as your schema grows. Stick to single-column surrogate keys whenever possible.
• Assuming Natural Keys stay constant: I have seen many production systems fail because they used "Customer Code" as a PK, only for the business to decide to re-brand and change all customer codes, forcing a massive, dangerous database migration.
• Exposing Surrogate Keys in URLs: Avoid leaking your database user_id (like myapp.com/user/102) in public-facing URLs. It can reveal sensitive metrics about your growth. Use a separate public_id or UUID for those exposed routes.

FAQ

Can I have more than one primary key? No. A table can only have one primary key constraint. However, you can have multiple UNIQUE constraints on different columns.

Why not just use a GUID for everything? UUIDs are great for security and distributed systems, but they are larger (128-bit) than BIGINT (64-bit). In massive datasets, this can lead to larger index sizes and slightly slower performance. For most SaaS apps, BIGINT is perfectly fine.

Recap

Primary keys are the foundation of your schema. By preferring surrogate keys over natural identifiers, you decouple your data structure from business volatility, ensuring your database remains stable as your requirements evolve. Always ensure your PKs are unique, non-null, and immutable.

Up next: We will dive into Foreign Keys and Relationships to link these entities together into a functional schema.