A table might have repetitive data in form of rows. This might cause glitches while retrieving data from the query. To avoid them, a unique constraint is used. Unique allows sorting a column or set of columns uniquely meaning, a user cannot insert a duplicate or repeated value in a column as it results in an error. A unique constraint is enforced in a table while creating a table.
If a user wants to create a Student table and wants to enforce unique constraint, it can be done as follows –
create table Student (name varchar2(30), rollnumber int unique, age int)
Table – Student
In the table created above, rollnumber is enforced a unique constraint as each student is assigned different rollnumber to avoid confusions. For further understanding, an example is given below –
insert into student values ('Maya', '111', 'CSE');
It results in an error as 111 is already assigned to another student. This way unique constraint restricts the use of duplicates in a table.
Violation of UNIQUE KEY constraint ‘UQ__Student__EBE41F7A3D93XXXX’. Cannot insert duplicate key in object ‘Student’.
The duplicate key value is (111).
The statement has been terminated.
The primary key and Unique has the same functionality of enforcing uniqueness among a set of columns to avoid repetitions. The only difference between primary key and unique is that the primary key can be used only once while unique can be used more than once.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Check Constraint in MS SQL Server
- SQL | UNIQUE Constraint
- SQL | CHECK Constraint
- SQL | DEFAULT Constraint
- Foreign Key constraint in SQL
- Primary key constraint in SQL
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Neo4j Create Constraint
- Delete statement in MS SQL Server
- SQL Server Mathematical functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)
- SQL Server Identity
- SQL SERVER | Conditional Statements
- SQL Server | STUFF() Function
- SQL Server | SERVERPROPERTY()
- Mean and Mode in SQL Server
- Allow only alphabets in column in SQL Server
- SQL SERVER | IN Condition
- Reverse Statement Word by Word in SQL server
- Copy tables between databases in SQL Server using Import-and-Export Wizard
- Difference between MySQL and MS SQL Server
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.
Improved By : khushboogoyal499