SQLite Unique Constraint

  Total Views : 21
  Comments : 0
 
  Keywords : SQLite unique constraint on column with example, Use sqlite unique constraint to allow only unique values in column with example, SQLite add or remove unique constraint on multiple columns

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

SQLite Unique Constraint

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

 

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

Syntax of SQLite Unique Constraint

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

 

CREATE TABLE tablename

(col1 INTEGER UNIQUE,

col2 TEXT,

col3 TEXT);

In 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 single column.

 

CREATE TABLE BOOK

(Book_id INTEGER UNIQUE,

Book_name TEXT,

Publisher_name TEXT);

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

 

Now insert records in newly created table “BOOK” using 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 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 above example we are applying unique constraint on first_name and last_name columns of Employee_Master table.

 

Let’s insert some records to 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 above statements we are inserting “Shweta” two times in first_name column but it will throw any 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 above query it will throw error because we are inserting duplicate row in table.

 

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

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User