SQLite UNIQUE constraint ensures that columns contain unique values maintaining data integrity and preventing duplicate entries. In this comprehensive guide, you’ll learn how to use SQLite’s unique constraints to guarantee uniqueness in a column or combination of columns. So, let’s dive in!
SQLite UNIQUE constraints enforce uniqueness in one or more columns. They prevent duplicate values by restricting columns to only accept distinct values. Unique constraints differ from primary keys in that they allow NULL values. Defining uniqueness at the database level helps ensure data integrity.
Also read: The SQLite Handbook: A Start-to-Finish Resource for Learning and Using SQLite
What Are SQLite Unique Constraints?
A unique constraint ensures all values in a column or group of columns are distinct. It prevents duplicate entries by restricting a column or column to accept unique values.
Some key facts about SQLite’s unique constraints:
- They enforce uniqueness at the column level
- Can be applied to a single column or multiple columns
- Allow NULL values (NULLs are considered distinct)
- Created when table is created or added later
- Violating the constraint results in an error
Unique constraints differ from primary keys in that they allow NULL values. They are less strict than primary keys but still ensure no duplicates in a given column or column set.
Unique constraints are essential for data integrity. For example, you may want to prevent duplicate email addresses in a users table. Using a unique constraint on the email column would guarantee uniqueness.
Now, let’s look at the syntax for creating unique constraints in SQLite.
Syntax for Creating SQLite Unique Constraints
There are two ways to define an SQLite unique constraint:
1. Column Level
Specify UNIQUE when creating a table:
CREATE TABLE table_name (
column_name DATA_TYPE UNIQUE,
...
);
2. Table Level
Add a separate UNIQUE constraint definition:
CREATE TABLE table_name (
column_name DATA_TYPE,
...
UNIQUE (column_name)
);
Table-level is required for multi-column constraints:
CREATE TABLE table_name (
column_1 DATA_TYPE,
column_2 DATA_TYPE,
...
UNIQUE (column_1, column_2)
);
You can also name the constraint for clarity:
CONSTRAINT constraint_name UNIQUE (column_1, column_2)
Now let’s look at some examples of single and multi-column unique constraints.
Also read: SQLite Show Table Columns: A Detailed Guide
SQLite Unique Constraint Single Column Example
Here is an example of a unique constraint on a single column in SQLite:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT UNIQUE,
name TEXT
);
INSERT INTO users VALUES (1, '[email protected]', 'John');
INSERT INTO users VALUES (2, '[email protected]', 'Mary');
This creates a users table with a unique constraint on the email column. Trying to insert duplicate emails will result in an error:
INSERT INTO users VALUES (3, '[email protected]', 'John Doe');
/*
Error: UNIQUE constraint failed: users.email
The email '[email protected]' already exists in the table.
*/
The unique constraint prevents duplicate emails and ensures data integrity.
Also read: SQLite Upsert: Using INSERT ON CONFLICT
SQLite Unique Constraint Multiple Columns Example
To apply a uniqueness constraint across multiple columns, use a table-level constraint:
CREATE TABLE inventory (
id INTEGER PRIMARY KEY,
product_id INTEGER,
warehouse_id INTEGER,
quantity INTEGER,
UNIQUE (product_id, warehouse_id)
);
INSERT INTO inventory VALUES (1, 10, 1, 5);
INSERT INTO inventory VALUES (2, 20, 2, 10);
Here the combination of product_id and warehouse_id must be unique. Trying to insert rows with duplicate combinations will fail:
INSERT INTO inventory VALUES (3, 10, 1, 8);
/*
Error: UNIQUE constraint failed: inventory.product_id, inventory.warehouse_id
The pair of values '10, 1' already exists in the table.
*/
This ensures no duplicate inventory records for the same product and warehouse.
Handling NULL Values with SQLite Unique Constraints
NULL values are allowed in columns with unique constraints. SQLite considers each NULL as distinct.
Therefore, you can insert multiple NULLs in a unique column:
CREATE TABLE contacts (
id INTEGER PRIMARY KEY,
name TEXT,
phone TEXT UNIQUE
);
INSERT INTO contacts (name, phone) VALUES ('John', NULL);
INSERT INTO contacts (name, phone) VALUES ('Mary', NULL);
This inserts two rows with NULL phone numbers, which the uniqueness constraint allows. NULLs are treated as distinct values, but duplicates between NULL and non-NULL values are not allowed.
Adding SQLite UNIQUE to Existing Tables
To add a unique constraint to an existing table, you must:
- Rename the original table
- Create a new table with the constraint
- Copy data from old to new table
For example:
/* Existing table */
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT,
name TEXT
);
/* Add unique constraint */
ALTER TABLE users RENAME TO users_old;
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT UNIQUE,
name TEXT
);
INSERT INTO users SELECT * FROM users_old;
DROP TABLE users_old;
This recreates users with the unique constraint without losing any data.
Dropping Unique Constraints in SQLite
To remove a unique constraint, follow the same steps as adding a constraint:
- Rename original table
- Recreate table without constraint
- Copy data to new table
For example:
/* Table with unique constraint */
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT UNIQUE,
name TEXT
);
/* Remove constraint */
ALTER TABLE users RENAME TO users_old;
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT,
name TEXT
);
INSERT INTO users SELECT * FROM users_old;
DROP TABLE users_old;
This removes the unique constraint from the users table.
Real World Examples of SQLite Unique Constraints
Here are some real-world examples of using unique constraints in SQLite databases:
1. Preventing Duplicate User Accounts
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT UNIQUE,
password TEXT
);
A unique constraint on email prevents multiple accounts with the same email.
2. Ensuring Unique Product Names
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
description TEXT
);
The unique constraint guarantees each product has a distinct name.
3. Enforcing Unique Combinations of Category and Title
CREATE TABLE articles (
id INTEGER PRIMARY KEY,
category TEXT,
title TEXT,
content TEXT,
UNIQUE (category, title)
);
Articles can only have one unique combination of category and title.
4. Preventing Duplicates in an Audit Log
CREATE TABLE audit_log (
id INTEGER PRIMARY KEY,
user_id INTEGER,
log_time TEXT,
action TEXT,
UNIQUE (user_id, log_time)
);
The unique constraint prevents duplicate entries for a user action at a specific time.
Summary
UNIQUE constraints are an important tool for enforcing data integrity in SQLite. Key points:
- They prevent duplicate values in a column or set of columns
- Single or multiple columns can be constrained
- NULL values are allowed
- Constraints can be added/dropped from existing tables
- Violating the constraint results in an error
Defining uniqueness at the database level with constraints helps prevent bugs, bad data, and improve data quality. Now you have the knowledge to effectively use unique constraints in your own SQLite databases!