Unit

Primary Keys in Database Tables

Learn what primary keys are, why relational tables need them, and how to choose, define, and maintain effective single-column or composite keys in SQL.

A primary key is the column or combination of columns that uniquely identifies each row in a database table. It gives every stored record a dependable identity so that applications and SQL statements can address one specific row.

For example, a Students table might use student_id as its primary key. Two students may have the same name, but their identifiers must be different.

What a Primary Key Does

A table has one primary key constraint. That constraint can use one column or several columns. The selected key must satisfy two core rules:

These rules prevent duplicate row identities. They also make individual records addressable in statements such as UPDATE and DELETE:

UPDATE students
SET full_name = 'Jordan Lee'
WHERE student_id = 42;

Without a reliable identifier, a condition such as WHERE full_name = 'Jordan Lee' could affect several rows or the wrong row.

Primary Keys in Relational Design

In relational design, a table usually represents an entity or relationship. An entity might be a customer, order, student, or product. Its primary key establishes the identity of each instance of that entity.

Primary keys also support relationships between tables. A foreign key is a column or group of columns in one table that references a key in another table. The referenced primary key is the parent-side target.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name VARCHAR(150) NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Here, customers.customer_id identifies a customer, while orders.customer_id identifies which customer placed an order. The relationship uses a stable identifier instead of copying the customer's name into every order.

This supports:

• Data integrity: child rows can refer to valid parent rows.
• Joins: related rows can be matched accurately.
• Updates: a customer's descriptive information can be stored once.
• Deletions: foreign-key rules can prevent or control deletion of a referenced parent.

Characteristics of a Good Primary Key

When selecting a key, evaluate possible candidate keys. A candidate key is a minimal set of columns capable of uniquely identifying each row. Select one candidate key as the primary key; the remaining candidates become alternate keys.

A good primary key generally has these characteristics:

• It is unique for every record.
• It is always available and cannot be missing.
• It is stable and unlikely to change.
• It uses the smallest practical data type or number of columns.
• It does not depend on a business rule that may later change.
• It is straightforward for related tables to reference.

A meaningful-looking value is not automatically a good key. Names can repeat, email addresses can change or be shared, and externally assigned numbers may be corrected or recycled. A key should represent identity reliably, not merely contain useful information.

Natural Keys and Surrogate Keys

A natural key uses an existing real-world attribute, or combination of attributes, that is inherently unique in the relevant domain. For example, a standardized country code can be a natural key for a Countries table when the code standard is stable and appropriate to the application.

A surrogate key is an artificial identifier created solely to identify rows. It has no required business meaning. An auto-generated integer is a common surrogate key.

A surrogate key is often appropriate for customers, products, orders, and other entities when business identifiers can change or are governed by external systems. A business identifier can still have a UNIQUE constraint:

CREATE TABLE customers (
  customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  customer_name VARCHAR(150) NOT NULL
);

This design gives the customer a stable internal identity while enforcing email uniqueness according to the application's rules. Whether email should be unique is a business decision; shared addresses or multiple accounts may be valid in some systems.

Single-Column and Composite Primary Keys

A single-column primary key uses one column, such as student_id or product_id. It is usually simple to reference, join, index, and pass between applications.

A composite primary key, also called a compound primary key, uses two or more columns. Uniqueness applies to the complete combination, not necessarily to each component separately.

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_on DATE NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

A student may enroll in many courses, and a course may have many students. The pair (student_id, course_id) prevents the same student from being enrolled in the same course twice. A student_id value may appear many times, and a course_id value may appear many times; only the pair must be unique.

Composite keys are also useful for order lines:

CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  line_number INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_number)
);

product_id alone is not a suitable key because the same product can appear in many orders. The order and line number together identify one line within one order.

Composite keys can make foreign keys and joins more complex because child tables must carry and match every key component. Do not use one unnecessarily when a stable surrogate key plus suitable UNIQUE constraints is clearer.

Defining Primary Keys in SQL

Simple key declared inline

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL
);

Simple key declared at table level

CREATE TABLE departments (
  department_id INTEGER NOT NULL,
  department_name VARCHAR(100) NOT NULL,
  CONSTRAINT pk_departments PRIMARY KEY (department_id)
);

Table-level syntax is useful when naming a constraint or when declaring a composite key.

Generated identifiers

CREATE TABLE products (
  product_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(150) NOT NULL
);

An identity or auto-increment column lets the database generate identifiers. Exact syntax differs among database systems, so consult the documentation for the system you use. Generated values still must remain unique, and an application should not assume that an identifier communicates business meaning.

Adding a key to an existing table

ALTER TABLE departments
ADD CONSTRAINT pk_departments PRIMARY KEY (department_id);

Before adding the constraint, existing rows must have no duplicate values and no NULL values in the proposed key columns. For a composite key, no complete combination may be duplicated.

For more practice with table definitions and changes, see SQL Commands Syntax and Modify a Table.

Primary-Key Enforcement and Indexes

The database enforces a primary-key constraint when rows are inserted or changed. Application validation is useful for friendly error messages, but it is not a replacement for database enforcement because multiple applications, scripts, or concurrent requests may write to the same table.

An insert using a value already present in the primary key fails with a duplicate-key or unique-constraint error:

CREATE TABLE products_demo (
  product_id INTEGER PRIMARY KEY,
  product_name VARCHAR(150) NOT NULL
);

INSERT INTO products_demo (product_id, product_name)
VALUES (10, 'Keyboard');

INSERT INTO products_demo (product_id, product_name)
VALUES (10, 'Mouse'); -- rejected: product_id 10 already exists

For a non-generated primary key, omitting the value or supplying NULL also fails because every row needs an identifier. A generated key may be omitted when the database is responsible for producing it.

Database systems generally create or use an index to support primary-key lookups and uniqueness checks. The exact index type, name, and implementation vary by product. A primary key is therefore more than an index: it is a data-integrity constraint, while an index is primarily a lookup structure.

Primary Key, UNIQUE Constraint, and Foreign Key

For example, customer_id can be the primary key in customers and a foreign key in orders. It is not automatically unique in orders, because one customer can place many orders.

Changing and Maintaining Primary Keys

Changing a primary-key value can affect every child row that references it. Before changing or deleting a parent row, inspect its foreign-key dependencies and decide which referential action is appropriate.

• Restrict or reject: prevent a parent change while children refer to it.
• Cascade: propagate an approved update or deletion to dependent rows.
• Set NULL: remove the child reference when the relationship is optional and the column permits NULL.

The correct action depends on the data model and business rules. Stable surrogate keys can reduce the need to change identifiers when descriptive or external business values change.

Common Design Mistakes

• Using a name as a key: names can repeat, change, or be formatted differently.
• Using email as the only identity: addresses can change, be shared, or follow different uniqueness policies.
• Using an externally generated value without checking its lifecycle: an external identifier may be corrected or recycled.
• Leaving a table without a reliable row identifier: updates, deletes, joins, and references become ambiguous.
• Treating a non-unique column as a key: descriptive columns do not identify rows unless uniqueness is guaranteed.
• Using a composite key unnecessarily: extra columns increase foreign-key and join complexity.
• Confusing key types: a primary key identifies a row, a foreign key references another table, and an index speeds access.

Troubleshooting Primary-Key Problems

A primary key cannot be added

Inspect the proposed columns for duplicate values and NULL values. If the columns do not consistently identify rows, clean or merge duplicate records, assign valid identifiers where appropriate, and then retry the constraint.

An insert fails with a duplicate-key error

The application may have supplied an existing identifier, an automatic sequence may be out of sync after an import, or a junction-table relationship may have been submitted twice. Decide whether the operation should be an update, correct identifier generation, and prevent duplicate relationship submissions.

A primary-key value needs to change

This often indicates that a natural key was based on a changeable business value. Check foreign-key dependencies and configured referential actions before changing it. For future designs, consider a stable surrogate key if the business identifier is expected to change.

An ID exists but duplicate business records remain

A surrogate key guarantees that rows have different identities; it does not guarantee that their business attributes are different. Add a UNIQUE constraint to the appropriate business column or column combination when duplicates are invalid.

A composite key appears to allow duplicates

One component may repeat while the full combination remains unique. For example, several rows can use the same student_id if each row has a different course_id. Add another constraint only if an individual column must also be unique.

Exam- and Design-Review Notes

• A table has one primary key constraint, not necessarily one primary-key column.
• Primary-key values are unique and never NULL.
• Composite-key uniqueness applies to the complete combination.
• A foreign key commonly references a primary key, but it is not itself a primary key.
• A surrogate key does not replace a needed UNIQUE constraint on a business identifier.
• Choose candidate keys before selecting the primary key, and assess stability as well as uniqueness.
• Primary-key constraints are enforced by the database; application checks alone cannot guarantee integrity.

For related database fundamentals, review Create an Index and Advanced Select Statements.