Can a primary key contain several columns?

Yes. The combination must be unique even when individual components repeat.

Does PRIMARY KEY automatically generate an identifier?

No. Value generation requires a separate mechanism or application process.

Can a table have other unique identifiers?

Yes. Alternate candidate keys can be enforced with suitable constraints even though only one is designated primary.

AI Glossary / Data engineering and architecture / Intermediate

Primary key

Definition

A primary key is the column or combination of columns chosen to identify each row of a table uniquely. In conventional SQL design, its values are required and unique. A table can have several candidate keys, but one is designated primary. The choice expresses the main row identifier; it does not mean that the key contains the most important business information or that it automatically generates its own values.

What it means

An order identifier lets applications refer to one order even when its status, amount, or delivery address changes. A learner-course enrollment might instead require a combination of identifiers if that combination uniquely identifies the relationship. The key must match the table's intended grain. A field named ID is not automatically a good key, and a primary-key declaration cannot correct an unclear definition of what one row represents.

How it works

Identify candidate keys based on the business rules and choose a suitable primary key. Declare the constraint so the database rejects duplicate or missing key values according to its implementation. Other tables can reference it through foreign keys. A key can be natural, derived from meaningful business attributes, or surrogate, assigned mainly for identification. Generation mechanisms such as sequences or UUID creation are separate from the uniqueness constraint. Stability, scope, size, and integration requirements influence the choice.

Three practical examples

Order systems: OrderID identifies one order while order lines use their own identity or an order-and-line-number combination.
Course platforms: EnrollmentID identifies an enrollment attempt when learners can take the same course more than once.
AI experiment tracking: RunID identifies one model execution, while result rows use a key that also captures the relevant example and metric where required.

Think of it this way

Think of a catalog reference that points to one specific item. The reference remains useful even when descriptive details change. The analogy is limited because database keys have formal uniqueness and nullability rules, and a composite key may require several fields together. A catalog number also does not prove that the item was described correctly or that two catalogs use the same numbering scope.

Worked example

Suppose Enrollment contains LearnerID and CourseID. If each learner can enroll in a course only once, the pair may be a candidate key. The rows (L1,C1), (L1,C2), and (L2,C1) are distinct even though each individual column repeats. If repeat attempts become valid, a second (L1,C1) represents a new event but violates that key. The model needs an attempt component or a separate EnrollmentID, plus appropriate business constraints. Adding EnrollmentID does not automatically prevent accidental duplicate enrollment events, so the team must still define which combinations are disallowed. This example shows that a surrogate key can identify rows while additional uniqueness rules express the domain's meaning.

Illustrative case study

Fictional example: Hazel Courses uses email address as a learner key. A learner changes address, and some integrations treat the new address as a new person. The team evaluates a stable learner identifier and keeps email as a separately governed contact attribute. It also reviews whether shared or missing addresses are permitted. This invented case illustrates key stability considerations; it does not claim that email is never a valid candidate key in any carefully defined domain.

Common misconceptions and mistakes

Do not confuse a primary key with an index, although databases often use an index to enforce it. Do not assume a key must be a single integer or that generated values are gap-free. Avoid relying on a current sample's uniqueness as proof of a permanent business rule. Also distinguish row identity from real-world entity resolution: two rows with different keys may still describe the same person accidentally, and identifying that duplication requires additional evidence.

Business use

Good key design supports reliable references, updates, integration, and audit trails. Define the table's grain, uniqueness scope, and identifier lifecycle before choosing a representation. Test repeated events and data from multiple source systems. For AI datasets, distinguish entity, observation, document-version, and run identifiers; using the wrong grain can merge separate examples or duplicate one event under several keys. Keep meaningful alternate keys constrained where appropriate rather than assuming a surrogate primary key makes all other uniqueness rules unnecessary.

Check your understanding

A table adds a generated RowID but still permits two records for the same event that should occur only once. Has the primary key solved the business duplication problem?

Answer: No. It uniquely identifies the stored rows, but an additional rule is needed to prevent the duplicate event under the intended domain definition.

Related terms

Learn next

identify two possible keys for a small table and compare their stability and scope. Add a repeat-event scenario and a second source system. Decide which key remains suitable, which alternate constraints are needed, and how related tables should reference the chosen identity.

Sources

Sources: PostgreSQL 18, Constraints, https://www.postgresql.org/docs/18/ddl-constraints.html ; PostgreSQL 18, CREATE TABLE, https://www.postgresql.org/docs/18/sql-createtable.html ; Ontario Tech University, Functional Dependencies, https://csci3030u.science.ontariotechu.ca/chapter_3/chapter_3_section_1.html . Primary documentation and institutional teaching excerpts consulted for primary-key enforcement and candidate-key context. Examples are original; unusual product-specific legacy behavior is outside this conventional SQL explanation. Editorial source note: the Ontario Tech teaching page is secondary and model-attributed; it is not counted as primary verification. Other sources listed above provide the independent cross-checks.

Previous: Prim's algorithm Next: Prime Attribute