{"id":600,"date":"2018-06-03T18:15:43","date_gmt":"2018-06-03T11:15:43","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=600"},"modified":"2020-07-11T03:21:48","modified_gmt":"2020-07-10T20:21:48","slug":"sql-server-unique-constraint","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-unique-constraint\/","title":{"rendered":"SQL Server UNIQUE Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server UNIQUE<\/code> constraint to ensure the uniqueness of data contained in a column or a group of columns.<\/p>\n\n\n\n

Introduction to SQL Server UNIQUE constraint #<\/a><\/h2>\n\n\n\n

SQL Server UNIQUE<\/code> constraints allow you to ensure that the data stored in a column, or a group of columns, is unique among the rows in a table.<\/p>\n\n\n\n

The following statement creates a table<\/a> whose data in the email<\/code> column is unique among the rows in the hr.persons<\/code> table:<\/p>\n\n\n

CREATE<\/span> SCHEMA<\/span> hr;\nGO\n\nCREATE<\/span> TABLE<\/span> hr.persons(\n    person_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    first_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    last_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    email VARCHAR<\/span>(255<\/span>) UNIQUE<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you define the UNIQUE<\/code> constraint as a column constraint. You can also define the UNIQUE<\/code> constraint as a table constraint, like this:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> hr.persons(\n    person_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    first_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    last_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    email VARCHAR<\/span>(255<\/span>),\n    UNIQUE<\/span>(email)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement inserts a new row into the hr.persons<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> hr.persons(first_name, last_name, email)\nVALUES<\/span>('John'<\/span>,'Doe'<\/span>,'j.doe@bike.stores'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement works as expected. However, the following statement fails due to the duplicate email:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> hr.persons(first_name, last_name, email)\nVALUES<\/span>('Jane'<\/span>,'Doe'<\/span>,'j.doe@bike.stores'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server issued the following error message:<\/p>\n\n\n
Violation of UNIQUE KEY constraint 'UQ__persons__AB6E616417240E4E'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is (j.doe@bike.stores).<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
If you don’t specify a separate name for the UNIQUE<\/code> constraint, SQL Server will automatically generate a name for it. In this example, the constraint name is UQ__persons__AB6E616417240E4E<\/code>, which is not quite readable.<\/p>\n\n\n\n
To assign a particular name to a UNIQUE<\/code> constraint, you use the CONSTRAINT<\/code> keyword as follows:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> hr.persons (\n    person_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    first_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    last_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    email VARCHAR<\/span>(255<\/span>),\n    CONSTRAINT<\/span> unique_email UNIQUE<\/span>(email)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following are the benefits of assigning a UNIQUE<\/code> constraint a specific name:<\/p>\n\n\n\n
Behind the scenes, SQL Server automatically creates a UNIQUE<\/code> index<\/a> to enforce the uniqueness of data stored in the columns that participate in the UNIQUE<\/code> constraint. Therefore, if you attempt to insert<\/a> a duplicate row, SQL Server rejects the change and returns an error message stating that the UNIQUE<\/code> constraint has been violated.<\/p>\n\n\n\n