Description
By defining a column with SQL Unique Constraint, then that unique constraint column will not accept duplicate values. i.e, unique constraint will accept a single character or number. It will not accept repeated values in the table.
Syntax
create table <table_name>(<column_name1> datatype(size) constraint constraint name constraint type, <column_name2> datatype(size));
Table_name => Any accurate table in the database.
Column_name =>The operation that can be perform on a column in the table.
constraint_type =>constraint type will specify the type of constraint, that going to be executed in the database.
Examples
By viewing the below example, the concept of SQL Unique Constraint can be understood easily.
Case-1: Applying unique constraint on stu_id in student database table.
[c]sql> create table student(stu_id number(4)constraint student_stu_id_unique,stu_name varchar(255),fee int);
Query OK, 0 rows affected (0.32 sec)
sql> insert into student values(1001,'rock',12000);
Query OK, 1 row affected (0.16 sec)
sql> insert into student values(1002,'hairy',13000);
Query OK, 1 row affected (0.17 sec)
sql> insert into student values(1003,'scena',13000);
Query OK, 1 row affected (0.16 sec)
sql> insert into student values(1003,'joe',13000);
ERROR 1062 (23000): Duplicate entry '1003' for key 'stu_id'[/c]
Case-2: Applying unique constraint on stu_name in student database table.
[c]sql> create table student(stu_id number(4),stu_name varchar(255)constraint stud2_stu_name_unique,fee int);
Query OK, 0 rows affected (0.32 sec)
sql> insert into student values(1001,'rock',12000);
Query OK, 1 row affected (0.16 sec)
sql> insert into student values(1002,'hairy',13000);
Query OK, 1 row affected (0.17 sec)
sql> insert into student values(1003,'scena',14000);
Query OK, 1 row affected (0.16 sec)
sql> insert into student values(1004,'scena',15000);
ERROR 1062 (23000): Duplicate entry '1004' for key 'stu_name'[/c]
In the above example, for case:1 the SQL Unique Constraint is applied to column_name stu_id in which only unique stu_id's values will be accept, and duplicate stu_id values will not be accepted. And in case:2 the SQL Unique Constraint is applied to column_name in which only unique stu_name's values will be accept, and duplicate stu_names values will not be accepted.
Key Points
- SQL Unique Constraint - Unique constraint will not accept duplicate values.