Hello! Hope you guys are doing well. In this article, we will be unveiling one of the interesting and important topic in SQL — SQL UNIQUE Constraint.
What is a UNIQUE constraint?
SQL Constraints is a set of rules that are applicable to the schema or the data that is fed to the structure of a database. One such constraint is UNIQUE. The name ‘UNIQUE’ itself speaks about its nature and function.
SQL UNIQUE constraint enables the column to be free from redundant values. It helps restrict the input values to be unique. Thus, by applying a UNIQUE constraint to a column or a set of columns, we ensure that the data contains no Duplicate entries within it.
Let us understand the importance and functionality of UNIQUE constraint through a real time application/example.
Consider an online e-learning business portal. The owner of the business portal announces that he would grant free access to Data science courses in the coming time. In order to access the course material, the candidates would have to enter their email address and phone number.
Now, to make sure that a single candidate receives a single material kit at a time, the database engineer has to make sure that no repeated email ids and phone numbers must be accepted in the database.
This is where SQL UNIQUE constraint comes into the picture. Using UNIQUE constraint, no duplicate entries would be allowed. Thus, making sure that a single candidate does not grant access to multiple course material kits.
Having understood the working of SQL UNIQUE constraint, let us now understand the syntax of the same.
Syntax of SQL UNIQUE constraint
SQL UNIQUE constraint works with the structure as well as the data of the database.
-- Any data definition language command(CREATE/ALTER/DROP) column-name UNIQUE;
The SQL UNIQUE constraint is applied to one or multiple columns while defining the commands in the table or even can be applied while modifying the table(eg.ALTER command)
By applying UNIQUE rule to a column, the database accepts only ‘unique’ values for the specified column.
Implementing UNIQUE constraint through examples
We’ll use the SQL UNIQUE constraint in multiple different ways.
SQL UNIQUE CONSTRAINT with CREATE table command.
In this example, we create a table using SQL Create query and define the following columns:
CREATE TABLE INFO( ID INT NOT NULL, ACC_NO INT UNIQUE, NAME VARCHAR (20), PRIMARY KEY (ID) );
Here, the column ‘id’ is specified as NOT NULL(constraint), which means that the data values passed to this column can never be empty or null. Further, the column ‘ID’ is defined to behave as a PRIMARY KEY. SQL PRIMARY KEY too makes sure that the values passed to the table are non-redundant.
We have set the column ‘acc_no’ to UNIQUE by which the duplicate entries for this column would be forbidden. If we try to enter duplicate values for ‘acc_no’, duplicate value error would be raised.
We can set UNIQUE constraint for multiple columns of the table as shown below–
CREATE TABLE Sales ( ID int NOT NULL, NAME varchar(255) NOT NULL, Age int, Salary INT, CONSTRAINT Unique_sales UNIQUE (NAME,Age) );
In the above example, we have set UNIQUE constraint to two columns — ‘NAME’ and ‘Age’ and have provided a constraint name as ‘Unique_sales’.
SQL UNIQUE CONSTRAINT with ALTER table command
At times, if we wish to set the UNIQUE constraint to any data column after defining it, we can club it with SQL Alter table query.
SQL ALTER Table query along with UNIQUE constraint helps us modify the rules of the column and set unique rule/constraint to the specified column or columns.
Here, we have modified the above-created table – ‘Sales’ and have set Unique constraint to the column ‘Age’.
ALTER TABLE Sales MODIFY Age INT UNIQUE;
SQL UNIQUE CONSTRAINT with SQL DROP command
We can remove the rule/constraint from the columns with the help of SQL DROP command as shown below-
ALTER TABLE Sales DROP CONSTRAINT Unique_sales;
Point to Highlight!
As I had mentioned, that Primary key also ensures non-duplicate entries in the database.
Then, why has the need of using UNIQUE constraint arised??
Let me answer this question to the simplest terms.
SQL Primary Key serves the same purpose (like Unique constraint) of ensuring nonredundant values in the table. But, a table can have a SINGLE primary key while there can be multiple unique constraints applied to various columns of the table.
That’s all for this topic. Please feel free to comment below in case you come across any doubt.
For more such posts related to SQL, do visit SQL JournalDev.