Can a table have several unique keys?

Yes. Several column combinations can be protected by separate uniqueness rules.

Does unique mean not null?

Not necessarily for a SQL unique constraint. Check the implementation and add the required presence rule explicitly.

Is a foreign key the same thing?

No. A foreign key expresses a relationship to referenced data; uniqueness prevents prohibited repetition within its defined scope.

AI Glossary / Data engineering and architecture / Beginner

Unique key

Definition

A unique key is a column, or combination of columns, whose values distinguish records within a defined database scope. In SQL practice, the term commonly refers to columns protected by a uniqueness constraint. The exact handling of missing values depends on the database and constraint definition, so uniqueness should not automatically be interpreted as requiring every value to be present.

What it means

A business may need one account number per account or one product code per supplier. A unique key turns that expectation into an explicit rule about records.

The scope matters. Product code P17 could legitimately appear for two suppliers, while the combination of supplier and product code must be unique. A key describes which combinations identify records; it does not guarantee that the record's name, price or other information is correct. For AI pipelines, sound keys help avoid accidental duplication and ambiguous joins before model training or retrieval begins.

How it works

A database checks the constrained value or combination when relevant data is inserted or changed. A conflicting operation cannot simply create another committed record with the prohibited duplicate combination. The database's transaction and constraint rules determine when checking occurs and how competing operations are handled.

In PostgreSQL, a unique constraint creates a unique B-tree index. Its default treatment permits multiple nulls, while NULLS NOT DISTINCT changes that comparison. Other systems may differ.

A primary key adds a designated row-identification role and disallows nulls. A table can have one declared primary key and additional unique constraints for other rules. When modeling identifiers, specify required presence separately instead of assuming the word unique settles it.

Three practical examples

1. Customer administration: An internal account number must not be assigned to two account records. An update that would create that collision is rejected and reviewed.

2. Marketplace catalog: A seller can reuse a product code used by another seller. A combined seller-and-code key prevents duplicates within each seller's catalog without prohibiting legitimate codes elsewhere.

3. AI data preparation: A table stores one annotation per document and labeling round. A combined document-and-round key allows later rounds while preventing accidental duplicate annotations for the same round.

Think of it this way

Think of assigned seats in a venue. Seat 12 is ambiguous unless you also know the row. The pair “row C, seat 12” identifies one seat within that venue's seating plan.

The analogy concerns identity within a scope. It does not prove that the person holding a ticket is authorized to enter, and it does not explain database nulls or concurrency. Similarly, a unique database identifier is not an access-control credential.

Worked example

Consider a fictional catalog with three records: supplier S1 and code P7; supplier S1 and code P8; supplier S2 and code P7. Define both columns as required and protect their combination with a uniqueness constraint.

All three records satisfy the rule. P7 repeats, but its supplier differs. A proposed fourth record containing S1 and P7 conflicts with the first record and must not become an additional row under this design.

Making product code alone unique would incorrectly reject the valid S2 record. Making supplier alone unique would incorrectly reject S1's second product. Two separate single-column constraints therefore express a different and much stronger rule than one combined constraint.

Before implementation, the team also decides how code comparison works. If P7 and p7 should represent the same business code, the storage and comparison design must enforce that decision; the business cannot assume every database configuration compares them identically.

Illustrative case study

Illustrative fictional case: KeyOrchard combines product feeds for an AI shopping assistant. Its initial table uses a generated row number as the primary key. Because each import receives new row numbers, repeated feed deliveries create duplicate business records despite every primary key being different.

The team adds an explicit uniqueness rule for supplier and supplier product code after reviewing existing collisions. It separates genuine updates from conflicting source records and retains an exception report for unresolved cases.

The redesigned importer is tested with repeated files, changed descriptions and two suppliers sharing a code. The story illustrates why a generated identifier alone does not enforce business identity. It makes no claim about measured conversion rates, accuracy improvements or automated resolution of every historical duplicate.

Common misconceptions and mistakes

A unique key is not necessarily a single column. Composite keys enforce uniqueness of the combination.

A unique identifier does not prove that two records represent different real-world entities. Two accounts with different identifiers may still refer to the same customer, requiring a separate entity-resolution process.

Do not replace database enforcement with a simple application check that searches for duplicates and then inserts. Concurrent requests can both pass that preliminary check. Also, do not add a uniqueness rule merely because existing sample data happens to contain no repeats: the rule must describe legitimate future records too.

Business use

Use unique keys to make business identity rules explicit at the data boundary. Ask the process owner what must never repeat, within which organization or period, and whether identifiers can be changed or reused.

Review historical collisions before activating a new constraint. Decide how users and import processes should respond when a conflict occurs. Track whether a rejection represents a duplicate submission, a mistaken identifier or an overly restrictive rule. These distinctions make operational handling clearer than treating every constraint error as the same data-quality problem.

Check your understanding

Should UNIQUE(supplier, code) reject S1/P7 and S2/P7?

Answer: No. The combinations differ. Rejecting both merely because P7 repeats would require a different rule on code alone.

Related terms

Learn next

Write three valid and three invalid sample records for a proposed business key. Include missing values, reused codes and different organizations, then check whether the proposed database rules accept exactly the intended cases.

Missing values

Sources

PostgreSQL documentation, Constraints, unique constraints and primary keys: https://www.postgresql.org/docs/current/ddl-constraints.html

The relevant implementation rules were read. PostgreSQL-specific null and index behavior is explicitly scoped. Catalogue records and KeyOrchard are original fictional teaching examples, not reported customer outcomes.

Previous: Unique action axioms Next: Unique name assumption