SQLite UNIQUE Constraint

SQLite is a lightweight relational database management system (RDBMS). It requires minimal configuration and it is self-contained. It is an embedded database written in C language. It operates a server-less, file-based database engine making it a good fit for mobile applications and simple desktop applications. It supports standard SQL syntax. In this article, we are going to cover all the necessary points of UNIQUE constraint. This article will cover every possible use of UNIQUE constraints.

UNIQUE Constraint

UNIQUE constraints in SQLite ensure that a column or group of columns should not accept duplicate values. Simply, it prevents us from entering duplicate values in a column or group of columns.

Suppose, We create a table ‘student’. It consists of name, roll, and rank. As for its columns, we do not want the rank column to accept duplicate values but it can accept null values. Now, in this type of scenario, we can take the use of UNIQUE constraints into action.

Syntax :

CREATE TABLE table_name(

column_name datatype UNIQUE

);

Examples of UNIQUE Constraint

Let see some examples of unique constrain.

Example 1 : Unique Constraint in One Column

To implement UNIQUE Constraint lets create a table first.

Table Name : geeksforgeeks

CREATE TABLE geeksforgeeks (
    user_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    rank INTEGER UNIQUE,
    overall_score INTEGER,
    monthly_score INTEGER
);

In this example we have made rank as our unique column. This means this column cannot accepts duplicate values.

After successful execution of the command ,we can now insert data and display our table by

---Data Insertion
INSERT INTO geeksforgeeks(user_id,name,rank,overall_score,monthly_score)
VALUES (108,'Vishu',01,1890,45);
INSERT INTO geeksforgeeks(user_id,name,rank,overall_score,monthly_score)
VALUES (109,'Ayush',02,1880,40);
INSERT INTO geeksforgeeks(user_id,name,rank,overall_score,monthly_score)
VALUES (110,'Neeraj',03,1878,35);
INSERT INTO geeksforgeeks(user_id,name,rank,overall_score,monthly_score)
VALUES (111,'Sumit',04,1875,32);

---Displaying Data
SELECT * FROM geeksforgeeks;

Output:

geeksforgeeks – table

Now if I try to insert a duplicate value into my rank column, it will throw me an error.

INSERT INTO geeksforgeeks(user_id,name,rank,overall_score,monthly_score)
VALUES (112,'Karan',02,1874,30);

Output:

Error 01

Example 2: Unique in More Than One Column

We can create multiple unique columns in a table as much as we need. Lets continue the table from previous example. Now we decide to make columns : rank as well as montly_score unique. We can simply achieve it by below command.

CREATE TABLE geeksforgeeks (
    user_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    rank INTEGER,
    overall_score INTEGER,
    monthly_score INTEGER,
    unique (rank,monthly_score)
);

Now table ‘geeksforgeeks’ cannot accept duplicate values in both ‘rank’ and ‘montly_score’ columns collectively.

INSERT INTO geeksforgeeks(user_id,name,rank,overall_score,monthly_score)
VALUES (112,'Karan',02,1675,40);

Output:

Error 02

Note: We can duplicate one column value and keeping other unique may not cause an error.

Difference Between Primary Key and UNIQUE Constraint

Primary Key and Unique Constraint , both are used in relational database to ensure uniqueness but they are some different/unique aspects in both. Lets discuss some of these aspects :-

1. Primary key do not allows NULL values but on the other hand UNIQUE constraint allows NULL value.
2. We can have only one primary key in our table, on the other hand we can have more than one UNIQUE columns in our table.

Null Values in UNIQUE Columns

UNIQUE columns cannot accepts duplicate values however it can accept more than one null values. It has shown in the below example.

INSERT INTO geeksforgeeks(user_id,name,rank,overall_score,monthly_score)
VALUES (205,'Harsh',NULL,1900,20);

INSERT INTO geeksforgeeks(user_id,name,rank,overall_score,monthly_score)
VALUES (200,'Karan',NULL,1905,10);

Output:

geeksforgeeks-table01

Conclusion

Unique Constraint is a crucial part of relational database system (RDBMS). It gives us flexibility to make our columns to accept only unique values. However, unlike primary key we can add NULL values in our Unique columns. We can also have more than one unique columns in our table. Unique constraint ensures us that our columns should not accept any duplicate values.

In this article we have try to cover all the aspects of the unique constraints with brief and clear cut examples. We have also provided detailed explanation for each stated example.