SQLite Unique Constraint

Here we will learn SQLite unique constraint with examples and how to add SQLite unique constraint on a single column or multiple columns to allow only unique values with examples.

SQLite Unique Constraint

In SQLite, a Unique constraint is used to set restriction on a column to allow only unique values in the column.

 

The SQLite Unique Constraint same as the Primary Key constraint except that Unique Constraint will accept single Null value but the Primary Key will not accept any Null values and we can create a number of Unique Constraints on the table but we can create only one Primary Key constraint on a table.

Syntax of SQLite Unique Constraint

Following is the syntax of creating SQLite Unique Constraint on the column.

 

CREATE TABLE tablename

(col1 INTEGER UNIQUE,

col2 TEXT,

col3 TEXT);

In the above syntax we are adding Unique Constraint on single column “col1” then “col1” will not allow any duplicate values.

Example of UNIQUE Constraint on Single Column

Following is the example of creating Unique Constraint on a single column.

 

CREATE TABLE BOOK

(Book_id INTEGER UNIQUE,

Book_name TEXT,

Publisher_name TEXT);

If you observe the above example we are adding a UNIQUE constraint on BooK_id column in the CREATE TABLE statement.

 

Now insert records in the newly created table “BOOK” using the following statements.

 

sqlite> INSERT INTO BOOK Values (1,'SQLITE','Shweta');

 

sqlite> SELECT * FROM BOOK;

 

Book_id     Book_name   Publisher_name

----------  ----------  --------------

1           SQLITE      Shweta

Here we didn't get any error because Book_id column contains only one record with Id 1. Now we will insert another record using following statement.

 

sqlite> INSERT INTO BOOK Values (NULL,'SQLITE Guide','Shweta');

 

sqlite> SELECT * FROM BOOK;

 

Book_id     Book_name   Publisher_name

----------  ----------  --------------

1           SQLITE      Shweta

            SQLITE Gui  Shweta

Here, also we won't get any problem because in UNIQUE constraint NULL values are considered as distinct from all other values.

 

Now if we try to insert NULL or 1 value in Book_id column then UNIQUE constraint is violated.

 

sqlite> INSERT INTO BOOK Values (1,'SQLITE','Shweta');

 

Error: UNIQUE constraint failed: BOOK.Book_id

If you observe above query we tried to insert existing value “1” in Book_id column that's the reason it thrown error.

Example of UNIQUE Constraint on Multiple Columns

If we want to create Unique Constraint on multiple columns then we must apply it as a table-level constraint. Following is the example of creating Unique Constraint on multiple columns in the table.

 

CREATE TABLE Employee_Master

(ID INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT NOT NULL,

last_name TEXT NOT NULL,

CONSTRAINT con_emp_name_unique UNIQUE (first_name,last_name));

If you observe the above example we are applying unique constraint on first_name and last_name columns of Employee_Master table.

 

Let’s insert some records to the Employee_Master table using following statements.

 

INSERT INTO Employee_Master(first_name,last_name)

VALUES('Shweta','Jariwala');

INSERT INTO Employee_Master(first_name,last_name)

VALUES('Shweta','Rana');

If you observe the above statements we are inserting “Shweta” two times in the first_name column but it will throw an error because it will consider first_name and last_name combo values must be unique so when we run above statements the records will insert into table without having any problem.

 

Now let’s insert record with duplicate values in both columns.

 

INSERT INTO Employee_Master(first_name,last_name)

VALUES('Shweta','Jariwala');

 

Error: UNIQUE constraint failed: Employee_Master.first_name, Employee_Master.last_name

When we run the above query it will throw an error because we are inserting a duplicate row in table.

 

This is how we can use SQLite Unique Constraint to allow only unique values in a table based on our requirements.