SQL UNIQUE Constraint

Updated April 8, 2023

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.

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:

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.

The below Snapshot shows the 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’.

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.

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:

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