Get Started: SQL UNIQUE Constraint

In This Article, You Will Know About SQL UNIQUE Constraint.

Before moving ahead, let’s know a bit about the SQL ALTER TABLE Statement.

Table of Contents

SQL UNIQUE Constraint

The UNIQUE Constraint makes sure that all values present in the table should be different meaning that not a single value should be duplicated.

The  UNIQUE and PRIMARY KEY Constraints both make sure that not a column or a set of columns can be duplicated meaning that it guarantees the uniqueness of a column and a set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, SQL allows us to use many UNIQUE constraints per table but does allow only one PRIMARY KEY Constraint per table.

Note: You will learn more in detail about PRIMARY KEY 

SQL UNIQUE Constraint on CREATE TABLE

Example: Use UNIQUE Constraint to accept only different values in column “Section”.

				
					CREATE TABLE Student_info (
Name char(10) NOT NULL,
Class char(5) NOT NULL,
Section char(1) NOT NULL UNIQUE
);
				
			

Add UNIQUE Constraint to multiple columns when the table is created.

Syntax

				
					CREATE TABLE table_name 
ADD Constraint constraint_name UNIQUE (column1, column2,...);
				
			

Example: Use NOT NULL statements not to accept any null value in multiple columns.

				
					CREATE TABLE Student_info (
Name char(10) NOT NULL,
Class  char(5) NOT NULL,
Section char(1) NOT NULL UNIQUE,
Roll_no int,
CONSTRAINT Table_Constraint UNIQUE (Name, Class, Section) 
);
				
			

SQL UNIQUE Constraint on ALTER TABLE

Syntax

				
					ALTER TABLE table_name 
ADD Constraint constraint_name UNIQUE (column1, column2,...);
				
			

Example: Use UNIQUE Constraint on column “Section” after creating the table.

				
					ALTER TABLE Student_Info
ADD UNIQUE (Section); 
				
			

Add UNIQUE Constraint to multiple columns when the table is already created.

Syntax

				
					ALTER TABLE table_name 
ADD Constraint constraint_name UNIQUE (column1, column2,...);
				
			

Example: Use UNIQUE Constraint on column “Section” after creating the table.

				
					ALTER TABLE Student_Info
ADD CONSTRAINT Table_Constraint UNIQUE (Name, Class); 
				
			

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL Syntax:

				
					ALTER TABLE table_name 
DROP Constraint constraint_name;
				
			

Example: Use DROP constraint to drop a UNIQUE constraint.

				
					ALTER TABLE Student_Info
DROP UNIQUE Table_Constraint;
				
			

If you find anything incorrect in the above-discussed topic and have further questions, please comment below.

Connect on:

Leave a Comment

Stay in the loop

codingstreets