SQLite UNIQUE Constraint

The SQLite UNIQUE constraint is used to ensure that all values stored in a column, or a combination of columns, are unique. It prevents duplicate values from being inserted into a table and is commonly used for fields such as email addresses, usernames, employee IDs, phone numbers, and other business identifiers.

In this tutorial, we will understand what the SQLite UNIQUE constraint is, how to define it, how it works with multiple columns, and how to handle duplicate values with practical SQL examples.

What is the SQLite UNIQUE Constraint?

The UNIQUE constraint in SQLite ensures that no two rows contain the same value in the constrained column or set of columns.

For example, suppose an application stores user information. Each user should have a unique email address. We can define the email column with a UNIQUE constraint so that SQLite prevents duplicate email addresses.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT,
    email TEXT UNIQUE
);

In this example, SQLite allows different users to have different email addresses, but it will reject an attempt to insert the same email address more than once.

Why Use a UNIQUE Constraint?

A UNIQUE constraint is useful when a database column represents a value that must not be duplicated.

Common examples include:

• Email addresses
• Usernames
• Employee IDs
• Customer numbers
• Product codes
• Order reference numbers
• Phone numbers

Enforcing uniqueness at the database level is important because it protects data integrity even when multiple applications, scripts, or users access the database.

SQLite UNIQUE Constraint Syntax

A UNIQUE constraint can be defined directly on a column or separately at the table level.

Column-Level UNIQUE Constraint

CREATE TABLE table_name (
    column1 TEXT UNIQUE
);

Example:

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

Table-Level UNIQUE Constraint

A UNIQUE constraint can also be defined after the column definitions.

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    email TEXT,
    UNIQUE (email)
);

Both approaches enforce uniqueness for the email column.

SQLite UNIQUE Constraint Example

Let’s create a simple students table where every student must have a unique email address.

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

Now insert some records:

INSERT INTO students (name, email)
VALUES ('John', 'john@example.com');

INSERT INTO students (name, email)
VALUES ('Mary', 'mary@example.com');

Both statements execute successfully because the email addresses are different.

Attempting to Insert a Duplicate Value

Now try to insert another student using an existing email address:

INSERT INTO students (name, email)
VALUES ('David', 'john@example.com');

SQLite will reject the operation because john@example.com already exists in the email column.

You will typically receive an error similar to:

UNIQUE constraint failed: students.email

This behavior prevents duplicate values from being stored in the column.

SQLite UNIQUE Constraint with Multiple Columns

SQLite also supports a UNIQUE constraint across multiple columns. This is called a composite UNIQUE constraint.

Consider a table that stores course enrollments:

CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY,
    student_id INTEGER,
    course_id INTEGER,
    UNIQUE (student_id, course_id)
);

Here, the combination of student_id and course_id must be unique.

For example:

INSERT INTO enrollments (student_id, course_id)
VALUES (101, 501);

INSERT INTO enrollments (student_id, course_id)
VALUES (101, 502);

Both records are valid because their combinations are different.

However, the following statement will fail:

INSERT INTO enrollments (student_id, course_id)
VALUES (101, 501);

The combination (101, 501) already exists, so SQLite will report a UNIQUE constraint violation.

UNIQUE Constraint vs PRIMARY KEY

The UNIQUE constraint and PRIMARY KEY both help maintain uniqueness, but they serve different purposes.

A table can therefore have one primary key and several UNIQUE constraints.

SQLite UNIQUE Constraint and NULL Values

One important behavior to understand is how SQLite treats NULL values in a UNIQUE column.

For example:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

You can insert multiple rows with NULL in the unique column:

INSERT INTO users (email) VALUES (NULL);
INSERT INTO users (email) VALUES (NULL);

These inserts are allowed because SQLite considers NULL values to be distinct for UNIQUE constraint purposes.

When an email address is required and must also be unique, it is common to combine NOT NULL and UNIQUE:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

Now every user must provide an email address, and the email address must be unique.

UNIQUE Constraint with NOT NULL

A common database design pattern is:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_code TEXT NOT NULL UNIQUE,
    customer_name TEXT NOT NULL
);

Here:

• customer_code cannot be NULL.
• customer_code cannot be duplicated.
• customer_name cannot be NULL.

This is useful for identifiers such as customer codes, registration numbers, or business reference numbers.

Adding a UNIQUE Constraint to an Existing SQLite Table

SQLite does not provide a general ALTER TABLE operation for directly adding a UNIQUE constraint to an existing column.

A common approach is to create a UNIQUE index:

CREATE UNIQUE INDEX idx_users_email
ON users(email);

This index ensures that duplicate non-NULL email values cannot be stored.

Before creating the index, existing duplicate values must be resolved. Otherwise, SQLite cannot create the UNIQUE index successfully.

SQLite UNIQUE Index

A UNIQUE index can enforce uniqueness independently of the table definition.

CREATE UNIQUE INDEX idx_employee_email
ON employees(email);

You can also create a composite UNIQUE index:

CREATE UNIQUE INDEX idx_student_course
ON enrollments(student_id, course_id);

This guarantees that the combination of student_id and course_id is unique.

Handling Duplicate Records

Applications often need to handle situations where an insert may violate a UNIQUE constraint. SQLite provides several conflict-resolution techniques.

Using INSERT OR IGNORE

INSERT OR IGNORE INTO users (email)
VALUES ('john@example.com');

If the email already exists, SQLite ignores the conflicting insert rather than returning a constraint error.

Using INSERT OR REPLACE

INSERT OR REPLACE INTO users (id, email)
VALUES (1, 'john@example.com');

INSERT OR REPLACE uses SQLite’s conflict-resolution behavior and can replace an existing conflicting row. Care should be taken because replacement can have effects different from a normal UPDATE operation.

Using UPSERT

Modern SQLite versions support UPSERT syntax, which is useful when you want to insert a row or update it when a UNIQUE conflict occurs.

INSERT INTO users (email)
VALUES ('john@example.com')
ON CONFLICT(email)
DO UPDATE SET email = excluded.email;

UPSERT is particularly useful when implementing insert-or-update application logic.

Complete SQLite UNIQUE Constraint Example

Let’s create a complete example for an employee database.

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_code TEXT NOT NULL UNIQUE,
    employee_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

Insert valid employees:

INSERT INTO employees
(employee_code, employee_name, email)
VALUES
('EMP001', 'John', 'john@example.com');

INSERT INTO employees
(employee_code, employee_name, email)
VALUES
('EMP002', 'Mary', 'mary@example.com');

Now try to insert a duplicate employee code:

INSERT INTO employees
(employee_code, employee_name, email)
VALUES
('EMP001', 'David', 'david@example.com');

The operation fails because EMP001 already exists.

Similarly, attempting to reuse an existing email address will also fail:

INSERT INTO employees
(employee_code, employee_name, email)
VALUES
('EMP003', 'David', 'john@example.com');

The UNIQUE constraint protects both business fields from duplicate values.

How SQLite Enforces the UNIQUE Constraint

When SQLite creates a UNIQUE constraint, it maintains the required uniqueness internally, typically using a unique index associated with the constraint. This allows SQLite to efficiently check whether a value already exists before accepting an insert or update.

For example, when the following constraint is defined:

email TEXT UNIQUE

SQLite checks the existing indexed values when a new email is inserted or an existing email is changed.

Advantages of the SQLite UNIQUE Constraint

The UNIQUE constraint provides several important benefits:

• Data integrity: Prevents accidental duplicate values.
• Business rule enforcement: Ensures fields such as email or employee code remain unique.
• Database-level protection: Rules remain enforced regardless of which application accesses the database.
• Efficient lookups: The associated unique index can also help query performance.
• Composite uniqueness: Multiple columns can be combined to enforce a unique business key.

Important Points to Remember

• A UNIQUE constraint prevents duplicate non-NULL values.
• A table can have multiple UNIQUE constraints.
• A UNIQUE constraint can be defined at the column level or table level.
• Multiple columns can be combined in a composite UNIQUE constraint.
• SQLite permits multiple NULL values in a UNIQUE column.
• Use NOT NULL UNIQUE when a value must always be present and must be unique.
• A UNIQUE index can be used to enforce uniqueness on an existing table.
• Applications can use conflict-resolution techniques such as INSERT OR IGNORE and UPSERT when appropriate.

Conclusion

The SQLite UNIQUE constraint is an essential database feature for maintaining data integrity and preventing duplicate values. It can be applied to individual columns or combinations of columns and is commonly used for fields such as email addresses, usernames, employee codes, customer IDs, and product codes.

Understanding how UNIQUE constraints work, particularly their behavior with NULL values, composite keys, unique indexes, and conflict handling, helps developers design reliable and consistent SQLite databases.

Frequently Asked Questions About SQLite UNIQUE Constraint

What does UNIQUE mean in SQLite?

UNIQUE means that duplicate non-NULL values are not allowed in the constrained column or combination of columns.

Can a SQLite table have multiple UNIQUE constraints?

Yes. A SQLite table can have multiple UNIQUE constraints on different columns or column combinations.

Does SQLite UNIQUE allow NULL values?

Yes. SQLite allows multiple NULL values in a UNIQUE column because NULL represents an unknown or absent value rather than a duplicate concrete value.

Can I use UNIQUE with NOT NULL?

Yes. Using NOT NULL UNIQUE is a common way to require a value while also preventing duplicates.

What happens when a duplicate value is inserted?

SQLite normally raises a UNIQUE constraint violation and rejects the conflicting operation unless an appropriate conflict-resolution strategy is used.

—

SQLite Tutorials

SQLite tutorials on this website:

• https://www.testingdocs.com/sqlite-database-tutorials/

For more information on SQLite, visit the official website:

• https://www.sqlite.org/