Unique Key in SQL and DBMS
Introduction
A Unique Key is a database rule that prevents the same value, or the same combination of values, from appearing more than once in a table. It is commonly implemented with the SQL UNIQUE constraint. If a user account table declares email as unique, the database can store john@test.com once, but it rejects another row that tries to use the same email under the database's comparison rules.
Uniqueness is more than a convenience for finding data. It protects business facts. Two active users may not share one login name, two products may not share one SKU inside the same catalog, and the same student should not be enrolled in the same course twice. Application validation can warn a user before submission, but only a database constraint can reliably protect the rule when many applications, imports, background jobs, and concurrent requests write to the same data.
This tutorial explains single-column and composite uniqueness, NULL behavior, Primary Key differences, candidate and alternate keys, indexes, foreign-key references, case and whitespace handling, soft deletion, migration, performance, testing, and practical design decisions. The central idea is simple: a Unique Key says that a value or tuple must not repeat, while the surrounding design determines the scope, lifetime, and meaning of that promise.
Basic Unique Key Syntax
The shortest form places UNIQUE after a column definition. In the following table, user_id is the main row identifier and email is an additional value that cannot be duplicated.
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
email VARCHAR(150) NOT NULL UNIQUE,
display_name VARCHAR(100) NOT NULL
);
The constraint applies to every insert and update. After one row contains john@test.com, an attempt to insert another row with that value fails. An update that changes a different user's email to the occupied value also fails. The rule is declarative: the schema states the required truth, and the database checks that truth regardless of which client sends the SQL.
The phrase Unique Key is widely used in tutorials and by some database products. Standard SQL more commonly describes the feature as a unique constraint. In practical discussion, both phrases usually refer to the same no-duplicates requirement, but it is useful to distinguish the business key being protected from the SQL mechanism that enforces it.
Why Databases Need Unique Constraints
Many values are expected to represent one real-world identity. An employee number points to one employee, an order number points to one order, and a barcode points to one catalog item. If duplicates enter the table, a lookup may return several rows when the application expects one. Billing, authentication, inventory, reporting, and integration logic can then act on the wrong record or fail unpredictably.
A preliminary SELECT in application code is not enough. Two requests can check for an email at nearly the same time, both observe that it is absent, and both attempt the insert. This check-then-write race occurs even when the code appears correct. A Unique Key resolves the race atomically inside the database: one transaction succeeds and the other receives a constraint violation.
Constraints also document the model. A developer reading UNIQUE (company_id, employee_number) can see that employee numbers are unique within a company, not globally. Query optimizers can often use the supporting structure for efficient lookups, and foreign keys may be able to reference the constrained columns. One declaration therefore improves integrity, communication, and access paths.
Unique Key Versus Primary Key
Primary Keys and Unique Keys both reject duplicate non-NULL values, but they play different roles. A Primary Key is the table's selected principal identity. A Unique Key enforces an additional uniqueness rule. A table has one Primary Key constraint, which may contain several columns, but it can have many unique constraints.
| Primary Key | Unique Key or constraint |
|---|---|
| Selected main identity of the row | Additional no-duplicates rule |
| Never permits NULL in any key column | NULL behavior depends on the DBMS and definition |
| One Primary Key constraint per table | Multiple unique constraints per table |
| Common default target for relationships | May also be referenced when eligible |
| Frequently uses a stable surrogate ID | Frequently protects business identifiers |
Consider an employee table with employee_id as its Primary Key, plus unique employee number and email values. The ID remains the stable internal identity even if an employee's email changes. The two Unique Keys prevent duplicate business identities. Choosing a surrogate Primary Key does not remove the need to enforce the natural uniqueness that exists in the business domain.
One Table Can Have Multiple Unique Keys
A row can participate in several independent uniqueness rules. An employee may have a company-issued number, a login email, and an identity-provider subject value. Each can require uniqueness for a different operational reason.
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
employee_number VARCHAR(30) NOT NULL,
email VARCHAR(150) NOT NULL,
identity_subject VARCHAR(120) NOT NULL,
CONSTRAINT uq_employee_number UNIQUE (employee_number),
CONSTRAINT uq_employee_email UNIQUE (email),
CONSTRAINT uq_employee_identity UNIQUE (identity_subject)
);
These constraints are not competing Primary Keys. They independently protect three facts while employee_id remains the chosen relational identity. Multiple unique constraints are normal in well-designed tables, provided each corresponds to a real and stable rule. Adding uniqueness merely because the current sample data happens to contain no duplicates is dangerous; the rule must remain true for every valid future state.
Column-Level and Table-Level Definitions
A single-column constraint can be written directly beside the column. Table-level syntax is more flexible and makes naming explicit. Both definitions below express the same uniqueness rule.
-- Column-level form
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
sku VARCHAR(50) NOT NULL UNIQUE
);
-- Named table-level form
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
sku VARCHAR(50) NOT NULL,
CONSTRAINT uq_products_sku UNIQUE (sku)
);
Named constraints are usually easier to operate in production. Error messages can identify a meaningful name, migration scripts can drop or replace the exact object, and schema review can understand the intent quickly. Teams commonly follow a convention such as uq_table_columns. The exact DDL used to remove or alter the constraint varies across database products, so migrations should follow the selected DBMS syntax rather than assume all systems treat constraints and indexes identically.
Composite Unique Keys
A unique constraint can contain two or more columns. The complete tuple must be different, although each individual column may repeat. This is often called a composite Unique Key.
CREATE TABLE enrollments (
enrollment_id BIGINT PRIMARY KEY,
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_at TIMESTAMP NOT NULL,
CONSTRAINT uq_enrollment_student_course
UNIQUE (student_id, course_id)
);
Student 101 can enroll in course 10 and course 20. Student 102 can also enroll in course 10. What cannot recur is the pair (101, 10). This pattern is common in junction tables because it prevents a many-to-many relationship from being recorded twice.
Column order does not change which exact tuples are considered duplicates, but it can affect how the supporting index serves queries. An index beginning with student_id naturally supports lookups by student and by student-plus-course. A query only by course_id may need another index, depending on the DBMS and workload. Integrity design comes first, followed by access-path design based on measured queries.
Scoped Uniqueness
Business values are frequently unique only within a parent scope. Employee number E001 may exist in several companies, and username alex may exist in several tenants. Declaring the business value alone as unique would incorrectly impose a global rule.
CREATE TABLE tenant_users (
user_id BIGINT PRIMARY KEY,
tenant_id BIGINT NOT NULL,
username VARCHAR(80) NOT NULL,
email VARCHAR(150) NOT NULL,
CONSTRAINT uq_tenant_username
UNIQUE (tenant_id, username),
CONSTRAINT uq_tenant_email
UNIQUE (tenant_id, email)
);
The scope column is part of the identity rule. Before defining a Unique Key, ask whether uniqueness belongs to the whole database, one organization, one store, one calendar period, one version, or only active rows. Many production defects come from enforcing the right value at the wrong scope.
Unique Key, Candidate Key, and Alternate Key
A candidate key is a minimal set of attributes capable of identifying every row. One candidate is selected as the Primary Key; the remaining candidates are alternate keys. SQL usually implements an alternate key with UNIQUE plus NOT NULL. For example, if both user_id and email are guaranteed complete, stable, minimal identifiers, choosing user_id as primary makes email an alternate key.
Not every unique constraint is automatically a candidate or alternate key. A nullable locker number may be unique whenever assigned but unavailable for many employees. A temporary import reference may be unique for processing but not a permanent identity. A composite constraint may prevent a duplicate relationship without identifying the entity itself. These rules are valuable, but their modeling purpose differs.
The distinction can be summarized as follows: candidate and alternate keys are relational modeling concepts, while UNIQUE is a SQL enforcement mechanism. A designer decides whether a value represents identity; the database constraint protects the declared non-duplication rule.
Unique Keys and NULL Values
NULL requires special attention because it represents missing or unknown information rather than an ordinary value. A Primary Key never allows NULL. A unique constraint's treatment of NULL varies by database product. Some systems allow several rows with NULL because unknown values are not considered equal. Some configurations or products restrict the number, and modern systems may offer syntax that treats NULL values as not distinct.
Do not rely on a remembered cross-database rule. Confirm the behavior of the exact DBMS, version, index type, and constraint definition used by the project. If the business identifier must always exist, declare it explicitly as NOT NULL UNIQUE. That combination communicates both requirements and avoids treating absent values as valid identities.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
email VARCHAR(150) NOT NULL,
tax_reference VARCHAR(40),
CONSTRAINT uq_customer_email UNIQUE (email),
CONSTRAINT uq_customer_tax_reference UNIQUE (tax_reference)
);
Email is mandatory and unique. Tax reference is optional, and its NULL behavior must be validated for the chosen platform. If only populated references must be unique, some systems support filtered or partial unique indexes as a precise implementation.
Duplicate Inserts and Updates
A constraint is checked whenever a statement could produce a duplicate. Suppose the first insert succeeds:
INSERT INTO users (user_id, email, display_name)
VALUES (101, 'john@test.com', 'John');
A second insert using the same email should fail even though its Primary Key is different.
INSERT INTO users (user_id, email, display_name)
VALUES (102, 'john@test.com', 'Jonathan');
-- Rejected: duplicate value for the email unique constraint
The same protection applies to updates. If Alice changes her email to John's existing email, the update is rejected. Applications should catch the database's integrity exception and translate it into a clear domain response, such as an HTTP 409 conflict or a field-level validation message. They should not expose vendor-specific SQL text to users, and they should not mistake an expected uniqueness conflict for a system outage.
Surrogate Primary Keys and Business Uniqueness
A generated numeric or UUID Primary Key makes relationships stable and compact, but it does not prevent duplicate business records. The following table can store two rows for the same employee number because each receives a different generated ID.
CREATE TABLE employees (
employee_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
employee_number VARCHAR(30) NOT NULL,
employee_name VARCHAR(100) NOT NULL
);
The design needs a separate unique constraint if employee number must not repeat.
ALTER TABLE employees
ADD CONSTRAINT uq_employees_employee_number
UNIQUE (employee_number);
This combination is common and effective: the surrogate value supplies stable internal identity, while the Unique Key protects domain identity. A surrogate ID should not be used as a reason to omit known natural rules. Otherwise the schema can represent the same real-world entity several times without technically duplicating its Primary Key.
Unique Keys and Foreign Keys
A Foreign Key normally references a Primary Key or another candidate key backed by an eligible unique constraint. Referencing a Unique Key can be appropriate when the referenced business identifier is stable, meaningful across systems, and guaranteed for the relationship's lifetime.
CREATE TABLE departments (
department_id BIGINT PRIMARY KEY,
department_code VARCHAR(20) NOT NULL UNIQUE,
department_name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
department_code VARCHAR(20) NOT NULL,
CONSTRAINT fk_employee_department_code
FOREIGN KEY (department_code)
REFERENCES departments (department_code)
);
The design is legal in many systems because department_code is unique. It still deserves caution. If codes are editable, every referencing row must follow the change or use an appropriate referential action. Referencing the stable surrogate department_id often reduces update coupling, while keeping department_code unique preserves the business rule. Product-specific requirements for referenced unique indexes and column order should be checked.
Unique Constraint Versus Unique Index
A unique constraint expresses data integrity: duplicates are invalid according to the schema. A unique index is an access structure that also refuses duplicate index keys. Many databases implement a unique constraint by creating or reusing a unique index, which makes the two appear interchangeable, but their intent and management interfaces are not always identical.
Use a constraint when stating a permanent business invariant. Use product-specific unique indexes when their additional capabilities are required, such as filtered or partial uniqueness, expression-based normalization, selected operator classes, or included columns. Schema inspection tools, foreign-key eligibility, error metadata, and drop syntax may treat constraints and indexes differently.
A useful review question is whether the object exists to describe valid data or mainly to support a physical access strategy. If duplicate values would be logically wrong, record that rule as clearly as the database permits rather than hiding it inside an unexplained performance index.
Performance and Query Behavior
Because a database usually maintains an index-like structure for uniqueness, equality lookups on the constrained columns can be efficient and return at most one matching non-NULL key. Login by email, lookup by order number, and product lookup by SKU commonly benefit. The optimizer also knows that the predicate cannot match several rows, which can improve cardinality reasoning.
The benefit has a write cost. Every insert, key update, and delete must maintain the structure and check for conflicts. Wide string keys consume more storage and memory than compact values. Too many overlapping unique constraints increase write work and can create redundant indexes. This does not justify removing valid integrity rules; it means designers should avoid duplicate definitions and choose appropriate data types, lengths, and column order.
Performance should be measured with representative volume and concurrency. A uniqueness rule is first an integrity decision. Its supporting structure can then be reviewed alongside query plans, secondary indexes, cache behavior, and write throughput.
Case Sensitivity, Collation, and Normalization
Whether John@Test.com and john@test.com are duplicates depends on collation, data type, expression, and database configuration. A case-insensitive collation may treat them as equal, while a case-sensitive comparison may allow both. Whitespace, Unicode normalization, punctuation, and locale rules create similar surprises.
The database rule must match the domain rule. For login email, a team may store a display form but enforce uniqueness on a normalized form such as a trimmed, lowercased value. Some systems support generated columns or expression-based unique indexes for this purpose. Others require the application to populate a separate normalized column that the database constrains.
CREATE TABLE app_users (
user_id BIGINT PRIMARY KEY,
email_display VARCHAR(150) NOT NULL,
email_normalized VARCHAR(150) NOT NULL,
CONSTRAINT uq_users_email_normalized
UNIQUE (email_normalized)
);
Normalization must be deterministic and shared by every writer. Trimming only in one web form does not protect imports or service integrations. Define canonicalization, enforce it consistently, and test values that differ only by case, surrounding spaces, or equivalent Unicode forms.
Email Uniqueness Requires a Business Decision
Email is a common example, but the correct rule is not universal. Some applications treat email as a globally unique login. Others permit one address for several dependent profiles, separate identities by tenant, or retain duplicate contact addresses while using a different login identifier. Email addresses also change, can be recycled by providers, and may have provider-specific equivalence rules that an application should not casually guess.
Ask what email means in the system: verified login identity, notification destination, contact attribute, or external account key. Decide whether uniqueness applies before verification, only among active accounts, per tenant, or forever. Then choose a constraint that represents that decision. The syntax is easy; defining the correct scope and lifecycle is the difficult part.
Soft Deletes and Conditional Uniqueness
Soft deletion marks a row inactive instead of removing it. A normal unique constraint still sees the inactive row, so creating a new account with its old username may fail. That may be correct if usernames can never be reused, or incorrect if the business permits reuse after deletion.
Possible designs include preserving lifetime uniqueness, changing the archived value, including a lifecycle discriminator in a composite rule, or using a filtered or partial unique index that covers only active rows. Each choice has consequences. Including a Boolean such as is_deleted can still allow only one deleted row for a given value, which may not satisfy repeated delete-and-recreate cycles. Product-specific conditional indexing is often more precise where available.
Define the historical rule explicitly: unique among all records, unique among current records, unique during a validity interval, or reusable after a retention period. Audit, restore, and legal-retention workflows must agree with that definition before the physical constraint is selected.
Temporal and Historical Data
Versioned tables often keep several rows for the same logical entity. A simple unique constraint on business ID would reject legitimate history. The key may need version number, effective-start timestamp, or another period attribute, such as UNIQUE (policy_number, version_number).
Preventing overlapping validity periods is more complex than preventing exact duplicate tuples. A normal Unique Key can ensure two versions do not share the same start timestamp, but it cannot prove that date ranges never overlap. Some database products provide exclusion constraints or temporal features; otherwise a transaction-safe design, carefully chosen locking, and specialized validation may be needed.
This illustrates an important boundary: UNIQUE answers whether key values repeat. It does not enforce every semantic rule involving ranges, conditional states, or cross-row calculations.
Many-to-Many and One-to-One Relationships
In a junction table, a composite Unique Key prevents the same relationship from appearing twice. The table may use the pair itself as a composite Primary Key, or it may use a surrogate Primary Key and retain the pair as a unique constraint.
CREATE TABLE user_roles (
user_role_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
role_id BIGINT NOT NULL,
assigned_at TIMESTAMP NOT NULL,
CONSTRAINT uq_user_role UNIQUE (user_id, role_id)
);
Uniqueness can also change cardinality. A Foreign Key in a child table normally permits many children to reference one parent. Adding UNIQUE to that Foreign Key means a parent value can appear in at most one child row, turning the relationship into one-to-zero-or-one from the constrained side.
CREATE TABLE employee_badges (
badge_id BIGINT PRIMARY KEY,
employee_id BIGINT NOT NULL UNIQUE,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);
The rule says an employee has at most one badge row. Making a Foreign Key unique accidentally is a common way to break an intended one-to-many model, so relationship cardinality should be reviewed whenever uniqueness is added.
Inventory, Orders, and Reservations
Real systems often need several scopes of uniqueness. A product SKU may be unique per seller, an order number may be unique per sales channel, and a reservation may be unique for a resource and time slot.
CREATE TABLE reservations (
reservation_id BIGINT PRIMARY KEY,
room_id BIGINT NOT NULL,
reservation_date DATE NOT NULL,
slot_code VARCHAR(20) NOT NULL,
customer_id BIGINT NOT NULL,
CONSTRAINT uq_room_date_slot
UNIQUE (room_id, reservation_date, slot_code)
);
This prevents exact double booking for predefined slots. If reservations have arbitrary start and end times, equality uniqueness is insufficient because two different ranges can overlap. The example works only when the slot model guarantees that equal slot codes represent the conflicting capacity.
Likewise, an inventory table might use UNIQUE (warehouse_id, product_id, bin_code) to ensure one balance row per storage location. The constraint should describe the grain of the table: exactly what one row represents.
Adding a Unique Constraint to Existing Data
Adding uniqueness to an established table may fail because duplicate or NULL data already exists. Profile the data before applying the migration.
SELECT email, COUNT(*) AS duplicate_count
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
For a composite rule, group by every constrained column. Also evaluate canonical forms if the future constraint will be case-insensitive or normalized. Once duplicates are found, business owners must decide whether records should be merged, corrected, deactivated, or preserved under a revised scope. Arbitrarily deleting one row can destroy relationships or history.
Large production tables may require an online or staged rollout: stop creating new duplicates, backfill normalized values, remediate existing conflicts, build the supporting structure with the least blocking method available, validate it, and finally make the rule authoritative. Plan for concurrent writes throughout the migration rather than assuming a one-time duplicate query freezes the table.
Changing or Removing Uniqueness
A rule may change when organizations merge, identifiers become reusable, or tenant scope is introduced. Removing a unique constraint is not merely a mechanical DDL operation. Applications may assume lookups return one row, foreign keys may depend on the constrained columns, upsert logic may target the unique key, and integrations may use it as an external identity.
Before changing the constraint, inventory those dependencies and decide how ambiguity will be handled. A migration from global email uniqueness to tenant-scoped uniqueness may add tenant_id to queries, API contracts, cache keys, and relationship definitions. Deploying code and schema in a compatible sequence avoids a period in which one layer assumes the old rule while another accepts the new data.
Concurrency and Transaction Safety
Unique constraints are essential under concurrency. Two workers may process the same event, two users may claim the same username, or a retry may repeat an earlier request. Both transactions can pass application-level checks before either commits. The database arbitrates the conflict at the point where its isolation and locking rules require.
Design the caller to handle the losing transaction. Depending on the operation, it may return a conflict, fetch the row created by the winner, retry with a different identifier, or treat the request as idempotent. Do not remove the constraint to avoid exceptions; make the conflict part of the expected control flow.
Upsert features can simplify this pattern, but their conflict target must match a real unique constraint or eligible unique index. They also need clear semantics for which fields may be updated when a duplicate is found. Concurrency correctness comes from the schema and transaction behavior together, not from a preliminary existence check.
Unique Keys Do Not Enforce Every Business Rule
A Unique Key can reject repeated values, but it cannot by itself validate format, range, ownership, state transitions, or aggregate limits. UNIQUE (room_id, slot_code) prevents an exact duplicate but does not know whether two different slot codes overlap. A unique email constraint does not prove the address is valid or verified. A unique order number does not prove that its prefix matches the order's region.
Use the right mechanism for each invariant: NOT NULL for required values, CHECK for row-level conditions, Foreign Keys for references, Unique Keys for non-duplication, and transaction-safe service or database logic for rules that compare ranges or aggregates. Strong schemas combine these mechanisms instead of expecting one constraint to solve unrelated problems.
Common Design Mistakes
One mistake is assuming that UNIQUE makes a column the table's Primary Key. It does not; the Primary Key is explicitly selected and carries the principal identity role. Another mistake is assuming uniqueness can only cover one column. Composite unique constraints are fundamental for scoped identities and relationship tables.
Designers sometimes make every apparently distinct column unique. Names, descriptions, prices, timestamps, and status values commonly repeat and should not be constrained without a business requirement. The opposite mistake occurs when a surrogate Primary Key is added and natural uniqueness is forgotten, allowing duplicate customers, products, or relationships.
Other failures include making a one-to-many Foreign Key unique, inferring a permanent rule from today's clean sample, overlooking NULL behavior, ignoring case and whitespace, and failing to define soft-delete reuse. Each error begins by treating uniqueness as a syntax choice rather than a domain statement.
Unique Key Testing Strategy
Tests should prove both accepted and rejected states. Start with one valid value, repeat it in a second insert, and confirm that the database rejects the duplicate. Insert a different value, then update it to an occupied value. Verify the API or service translates the violation into the expected response without leaking internal details.
For a composite rule, prove that each component can repeat individually while the complete combination cannot. Test NULL according to the selected DBMS and business policy. Exercise case variants, leading and trailing spaces, Unicode forms, maximum lengths, soft-deleted rows, tenant boundaries, and reuse after state changes.
Finally, run concurrent requests that attempt the same key. A test suite that only calls application validation sequentially does not prove the database rule works under races. Schema-level integration tests should inspect the actual constraint, not an in-memory approximation with different comparison behavior.
Unique Constraint Design Workflow
Begin with the business requirement, not the DDL. Define what one row represents and identify the value or value combination that must not repeat. Determine whether the rule is global or scoped, whether every component is mandatory, how values are normalized, and whether the identifier can change or be reused.
Define the row grain
-> identify the non-duplication rule
-> choose global or scoped uniqueness
-> choose one column or a composite tuple
-> define NULL, case, whitespace, and collation behavior
-> define active, deleted, and historical scope
-> inspect and clean existing data
-> create a named constraint or suitable unique index
-> test inserts, updates, concurrency, and migrations
Record the decision in schema documentation and tests. When product rules change, review the constraint instead of silently working around it in application code.
Complete Practical Example
CREATE TABLE employees (
employee_id BIGINT GENERATED ALWAYS AS IDENTITY,
company_id BIGINT NOT NULL,
employee_number VARCHAR(30) NOT NULL,
email_normalized VARCHAR(150) NOT NULL,
employee_name VARCHAR(100) NOT NULL,
created_at TIMESTAMP NOT NULL,
CONSTRAINT pk_employees
PRIMARY KEY (employee_id),
CONSTRAINT uq_employee_company_number
UNIQUE (company_id, employee_number),
CONSTRAINT uq_employee_company_email
UNIQUE (company_id, email_normalized)
);
This design gives every row a stable generated identity. Employee number and normalized email may repeat across companies but cannot repeat inside one company. The constraints express tenant scope directly. Both business values are mandatory, so NULL cannot bypass the intended rule.
Whether email should instead be globally unique is a product decision. If the organization operates one global identity system, the correct constraint could be UNIQUE (email_normalized). If employees can work for several companies under the same account, the model may need separate person, account, and employment tables. A constraint should follow the entity model rather than force a convenient but inaccurate structure.
Key and Constraint Comparison
| Key or constraint | Main purpose | Duplicate values | NULL consideration |
|---|---|---|---|
| Primary Key | Main row identity | Not allowed | Not allowed |
| Unique Key | Additional non-duplication rule | Not allowed for compared key values | DBMS and definition dependent |
| Candidate Key | Minimal possible row identity | Not allowed conceptually | Must identify every row |
| Alternate Key | Candidate not chosen as primary | Not allowed conceptually | Normally enforced as NOT NULL |
| Foreign Key | Valid relationship reference | Usually allowed | Allowed unless separately required |
| Composite Key | Key made from several columns | Complete tuple follows its key rule | Depends on the key type |
Remember the roles: the Primary Key answers, "Which row is this?" A Foreign Key answers, "Which valid row is this related to?" A Unique Key answers, "Which value or combination must not repeat?" The same column set can participate in more than one structural concept, but each constraint has a distinct responsibility.
Interview Questions and Answers
What is a Unique Key? It is a constraint that prevents duplicate values in one column or duplicate tuples across several columns.
How is it different from a Primary Key? A Primary Key is the selected main row identity and never permits NULL. A table has one Primary Key constraint but may have several unique constraints.
Can a Unique Key contain multiple columns? Yes. A composite unique constraint rejects a repeated complete combination while allowing individual components to repeat.
Can a Unique Key contain NULL? The behavior varies by DBMS and definition. Use NOT NULL UNIQUE when the business key must always be present.
Is a Unique Key the same as an alternate key? No. Alternate key is a modeling role for an unselected candidate key. UNIQUE is the SQL mechanism commonly used to enforce it.
Can a Foreign Key reference a Unique Key? Often yes, when the target columns have an eligible unique constraint and all product-specific requirements are met.
Does a unique constraint improve performance? Its supporting structure often helps equality lookups, but it also adds storage and write-maintenance cost. Integrity is its primary purpose.
Why is application validation insufficient? Concurrent writers can both pass a preliminary check. The database constraint atomically protects the shared data.
Practical Review Checklist
- Define the exact business fact that must not repeat.
- Confirm whether uniqueness is global or scoped by another column.
- Use a composite constraint when the complete tuple is the rule.
- Declare NOT NULL when every key value must exist.
- Verify NULL behavior in the actual database product.
- Define case, collation, whitespace, and Unicode comparison rules.
- Decide whether deleted and historical rows reserve the value.
- Keep business uniqueness even when using a surrogate Primary Key.
- Name constraints consistently for clear errors and migrations.
- Check for duplicate existing data before adding the constraint.
- Review Foreign Key, upsert, query, and integration dependencies.
- Test duplicate inserts, updates, composite values, and concurrency.
- Avoid redundant unique indexes that enforce the same rule.
- Revisit the constraint whenever identity or lifecycle rules change.
Key Takeaway
A Unique Key protects a database from duplicate values in a column or duplicate combinations across several columns. It is an essential way to encode business rules such as one username per tenant, one SKU per catalog, one student-course enrollment, or one order number per channel.
UNIQUE KEY
= NO DUPLICATE VALUE
OR
NO DUPLICATE COLUMN COMBINATION
The syntax is only the beginning. Correct design also defines scope, NULL policy, normalization, lifecycle, historical reuse, and concurrency behavior. Primary Keys provide the principal row identity, Foreign Keys protect relationships, and Unique Keys preserve every additional non-duplication rule. When these constraints accurately reflect the domain and are tested at the database boundary, they prevent duplicate identities before those defects spread through applications, reports, and integrations.