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 : w3wiki
CREATE TABLE w3wiki (
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 w3wiki(user_id,name,rank,overall_score,monthly_score)
VALUES (108,'Vishu',01,1890,45);
INSERT INTO w3wiki(user_id,name,rank,overall_score,monthly_score)
VALUES (109,'Ayush',02,1880,40);
INSERT INTO w3wiki(user_id,name,rank,overall_score,monthly_score)
VALUES (110,'Neeraj',03,1878,35);
INSERT INTO w3wiki(user_id,name,rank,overall_score,monthly_score)
VALUES (111,'Sumit',04,1875,32);
---Displaying Data
SELECT * FROM w3wiki;
Output:
Now if I try to insert a duplicate value into my rank column, it will throw me an error.
INSERT INTO w3wiki(user_id,name,rank,overall_score,monthly_score)
VALUES (112,'Karan',02,1874,30);
Output:
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 w3wiki (
user_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
rank INTEGER,
overall_score INTEGER,
monthly_score INTEGER,
unique (rank,monthly_score)
);
Now table ‘w3wiki’ cannot accept duplicate values in both ‘rank’ and ‘montly_score’ columns collectively.
INSERT INTO w3wiki(user_id,name,rank,overall_score,monthly_score)
VALUES (112,'Karan',02,1675,40);
Output:
Note: We can duplicate one column value and keeping other unique may not cause an error.
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.
Contact Us