EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL UNIQUE Constraint
 

SQL UNIQUE Constraint

Updated April 8, 2023

SQL UNIQUE Constraint

 

 

Introduction to SQL UNIQUE Constraint

The following article provides an outline for SQL UNIQUE Constraint. A single field or group of attributes that uniquely identify a record is referred to as a unique constraint. As long as the collection of values is distinct, some of the fields can have null values. Its purpose is to make sure that, if a column permits NULL, the index is generated automatically. When users specify a UNIQUE constraint would then ensure that no two rows within the table can contain the same value for the columns participating in that index and only one unique NULL value may be inserted in each of these columns.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

To protect the integrity, accuracy, and dependability of the data in a given column, constraints in SQL Server are typically established limits and rules that are applied in a single column or several columns governing the values permitted in the columns. In other terms, the data will be successfully inserted if it complies with the constraints criteria. The insert action will fail if the inserted data violates the defined constraint.

A few characteristics of unique constraints are:

  • On a table, multiple distinct keys could be assigned.
  • Unique and NONCLUSTERED indexes will be produced by default when using unique keys.
  • Typically, one or more columns from a particular database make up a unique key.
  • A NULL value may exist in a column, however, there can only be one NULL per column.
  • A Foreign key constraint may contain references to a unique constraint.

Key Takeaways

  •  In contrast to a PRIMARY KEY, a UNIQUE KEY produce a Distinct Index Data Page. Each table may include more than one UNIQUE KEY constraint.
  • The correct operation of relational database management systems depends on the use of both primary and unique keys.

How to Create SQL Unique Constraint?

SQL constraints allow ensuring that data contained in a table is unique.

unique constraint creates table statement:

The below syntax shows how to create a statement.

CREATE TABLE tb_name
(
col1 datatype [ NULL | NOT NULL ],
col2 datatype [ NULL | NOT NULL ],
...
CONSTRAINT constraint_name UNIQUE (col1, col2, ... col_n)
);

In the above syntax, we have used the Keyword UNIQUE as a Constraint concept in column-wise. And the example is given below.

Let’s make a small table Paced with ID and Name as the columns. The UNIQUE constraint supplied with the column definition ensures that the ID column cannot contain duplicate values.

Step 1:

Create a Table:

Code:

CREATE TABLE Paced( fid int, fd_name varchar(10),ldname varchar(8), gender varchar (7));
Query OK, 0 rows affected (0.14 sec)

Next, insert values into a respective table:

Code:

MariaDB [edu]> insert into Paced values(10,'sai','lakna','F');
MariaDB [edu]> insert into Paced values(20,'Truban','dikshit','M');
MariaDB [edu]> insert into Paced values(30,'George','paul','M');

After Inserting, a table consists of the following data.

Output:

SQL UNIQUE Constraint 1

Step 2:

Next, using ALTER command Unique Constraint can be used in a statement like:

Code:

MariaDB [edu]> alter table paced add constraint unique_id UNIQUE (fid);

If we always include duplicates, it will produce the following error. We need to Drop Unique restrictions in as to include duplicates.

Step 3:

See, now if we try to add a new row, it throws an error as ‘Duplicate Entry. Because Duplicate value is not valid. As we inserted a value ‘30’ in a new row which already exists in fid.

SQL UNIQUE Constraint 2

The below Snapshot shows the description of a table ’Paced’.

description of a table ’Paced’

SQL Unique Constraint Group

Including a multi-column unique constraint on a table that already exists is named Grouping. To add to an existing table we need to include ‘ALTER’. Both Column and Group Column are the same.

The examples are discussed below:

SQL Unique Constraint Columns

SQL first checks the available data in the columns to make sure that all entries are unique before adding a UNIQUE constraint to an existing column or set of columns in a table. SQL generates a warning and doesn’t add the UNIQUE constraint if it discovers duplicate values.

Here is an example of showing a Table ‘Software’ where the unique constraint is assigned to a column ‘soft_name’.

assigned to a column ‘soft_name’

So here, the database system will output an error message if users attempt to enter a new entry that would result in duplicate values in the username column.

SQL UNIQUE Constraint 5

The two attributes tr name and email in the above table ‘reservation’ have unique values in the user’s table. Additionally, we used the CONSTRAINT keyword to name the UNIQUE restrictions. If we choose, we can utilise this name to eventually lift the UNIQUE restriction. We added a list of comma-separated columns inside parenthesis after the UNIQUE keyword to construct a UNIQUE on multiple columns.

SQL Unique Constraint Delete

To remove specific constraints with the alter command. Columns in an underlying database can be added, removed, or modified using the ALTER statement. On the already-existing table, it is also used to add and remove different restrictions.

To remove current unique constraints, use the DROP CONSTRAINT statement in the ALTER TABLE statement.

Code:

ALTER TABLE tbname
DROP CONSTRAINT unique values;

Example using Reservation Table

Given below is the example mentioned:

Code:

MariaDB [edu]> insert into reservation values(123,'pearl','egmore','edu@gmail');
Query OK, 1 row affected (0.817 sec)
MariaDB [edu]> select * from reservation;
+--------+---------+---------+-----------+
| pnr_no | tr_name | address | email |
+--------+---------+---------+-----------+
| 123 | pearl | egmore | edu@gmail |
+--------+---------+---------+-----------+
1 row in set (0.075 sec)

Once adding is done. The next step is to delete a constraint.

Code:

MariaDB [edu]> alter table reservation DROP CONSTRAINT pr_m;

Output:

SQL UNIQUE Constraint 6

FAQ

Given below are the FAQs mentioned:

Q1. List the difference between primary key and unique constraint.

Answer:

  • Primary Key: Primary keys can never have a NULL value by default. Sometimes used to act as a unique identifier for specific table rows. In a table, only one primary key is possible.
  • Unique Key: There can only be one NULL value per unique key. This key, along with the primary key, establishes a specific row in a way that the primary key does not.

Q2. Give the Syntax for Delete Unique Constraint.

Answer:

ALTER TABLE
DROP CONSTRAINT column constraint name;

Q3. What are constraints?

Answer:

Constraints in SQL are the guidelines that the system upholds to enhance data integrity. The business rules for the data contained in a table are also specified using them. Whether at the column level or the table level, a user can define constraints. Only a column will be affected by the column-level limitations.

Conclusion

Therefore we have seen how to use UNIQUE constraints in various scenarios and also we have seen different examples of creating a database.

Recommended Articles

This is a guide to SQL UNIQUE Constraint. Here we discuss the introduction, and how to create SQL unique constraints. with examples. You may also have a look at the following articles to learn more –

  1. What is SQL DROP DB?
  2. SQL Schema
  3. sql_plus Commands
  4. SQL Wildcards

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW