Primary Key

What Is a Primary Key in a Database?

A primary key uniquely identifies each record in a relational table. Its values must be unique and cannot be empty, so the database can distinguish one row from another.

A table has one primary key constraint, using one or several columns. For example, a Customers table may use customer_id. Database systems commonly create an index for primary-key lookups and table relationships.

Primary Key Example and How Table Relationships Work

A primary key gives each record an identity that other tables can store as a foreign key.

1. In a Customers table, customer_id 1042 identifies one customer.
2. In an Orders table, order_id identifies each order.
3. The Orders table stores customer_id as a foreign key.
4. The database matches each order to its customer without relying on a name or email address that may change.

This makes queries such as “show all orders for this customer” accurate and maintainable.

Primary Key Constraints: The Core Rules

Primary key rules protect record identity.

• Values must be unique.
• Values cannot be NULL, so every row has an identifier.
• A key can use one column or a composite of multiple columns.
• Values should remain stable because changes can break relationships.

Primary Key vs Foreign Key vs Unique Key

These constraints serve different data-quality needs. “Secondary key” is an informal term, not a specific SQL constraint.

How to Choose and Create a Primary Key

Choose an identifier that will remain reliable as the application changes.

1. Identify a value that represents one record.
2. Confirm it is unique for current and future rows.
3. Prefer a generated surrogate ID when a natural value, such as an email address, can change or reveal sensitive information.
4. Use a composite key only when the combined rule is stable and meaningful.
5. Enforce the constraint in the database, not only application code.

Benefits of Primary Keys

A well-designed primary key makes data safer to manage.

• Prevents accidental duplicate records.
• Supports dependable joins between tables.
• Helps updates and deletes target the intended record.
• Provides a reference for APIs, links, and audit records.
• Enables foreign-key rules that protect relationship integrity.

Practical Limits and Common Design Mistakes

A generated ID does not replace other business rules.

• Do not use unstable values, such as editable email addresses, without change handling.
• Do not recycle identifiers after a record is deleted.
• Avoid wide composite keys copied into related tables.
• Do not assume an auto-increment ID makes a business value, such as an invoice number, unique.
• Avoid changing a key after other tables reference it.

Primary Keys in Applications Built With Emergent

When building a full-stack app with an AI app builder, describe entities and relationships clearly, such as customers, orders, and subscriptions. Emergent can help turn that description into an application, but developers should review identifiers, uniqueness rules, and foreign-key relationships before deployment. For related concepts, see this guide to an idempotency key, which helps prevent duplicate operations rather than duplicate database rows.