Prev Next
- The UNIQUE is a constraint in SQL which is used to identify each record uniquely in a table.
- If we try to insert/update duplicate values for the UNIQUE column, then, the query will be aborted.
- Syntax for SQL UNIQUE constraint is given below.
SQL Syntax for UNIQUE constraint:
| SQL Syntax for UNIQUE constraint | CREATE TABLE table_name (column_name1 data_type(size) NOT NULL UNIQUE, column_name2 data_type(size) NOT NULL, column_name3 data_type(size), etc…); |
Note: If we do not mention UNIQUE constraint, then, a table can hold duplicate values by default.
Example1 for how to use SQL UNIQUE constraint:
- Please execute below query in SQL to create a table with UNIQUE constraint.
| CREATE TABLE student (Student_ID int NOT NULL UNIQUE, Student_name varchar(255) NOT NULL, City varchar(255), Marks int); |
- Now, a new table called “student” is created and the field “Student_ID” is specified as NOT NULL UNIQUE. So, this column will be NOT NULL & UNIQUE.
Example2 for how to use SQL UNIQUE constraint on multiple columns:
- Please execute below query in SQL to create a table with UNIQUE constraint on multiple columns.
| CREATE TABLE student (Student_ID int NOT NULL, Student_name varchar(255) NOT NULL, City varchar(255), Marks int CONSTRAINT UNIQ_ID_NAME UNIQUE (Student_ID, Student_name)); |
- Now, a new table called “student” is created and the fields “Student_ID” & “Student_name” is together specified as UNIQUE.
- UNIQ_ID_NAME is the Constraint name. So, the combination of Student_ID and Student_name will be UNIQUE.