Internet, Networking, & Security Web Development UNIQUE Constraints in Microsoft SQL Server A UNIQUE constraint offers more flexibility than a primary-key constraint by Mike Chapple Writer Former Lifewire writer Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management. our editorial process Twitter Mike Chapple Updated on December 02, 2020 Tweet Share Email Web Development SQL CSS & HTML Web Design By creating a UNIQUE constraint, SQL Server administrators specify that a database column may not contain duplicate values. When you create a new UNIQUE constraint, SQL Server checks the column in question to determine whether it contains any duplicate values. If the table contains duplicates, the constraint creation command fails. Similarly, after you define a UNIQUE constraint on a column, attempts to add or modify data that would cause duplicates to exist also fail. KIVILCIM PINAR / Getty Images Why Use UNIQUE Constraints A UNIQUE constraint and a primary key both enforce uniqueness, but there are times that a UNIQUE constraint is the better choice. Use a UNIQUE constraint to specify several constraints to a table. You can only attach one primary key to a table.Use a UNIQUE constraint when a column permits null values. Primary key constraints can only be attached to columns that don't permit null values. Creating a UNIQUE Constraint The easiest way to create a unique constraint in Transact-SQL is to connect to a Database Engine in Object Explorer in SQL Management Studio and then click New Query. Use the following query, modifying the terms as necessary, to create a new table and add a constraint on a column: USE AdventureWorks2012; GO CREATE TABLE Production.TransactionHistoryArchive4 ( TransactionID int NOT NULL, CONSTRAINT AK_TransactionID UNIQUE(TransactionID) ); GO Execute the query. Similarly, to create a unique constraint on an existing table, execute the following T-SQL query: USE AdventureWorks2012; GO ALTER TABLE Person.Password ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt); GO UNIQUE Constraints vs. UNIQUE Indexes There has been some confusion about the difference between a UNIQUE constraint and a UNIQUE index. While you may use different T-SQL commands to create them (ALTER TABLE and ADD CONSTRAINT for constraints and CREATE UNIQUE INDEX for indexes), they have the same effect, for the most part. In fact, when you create a UNIQUE constraint, it actually creates a UNIQUE index on the table. Note several differences, though: When you create an index, you can add additional options to the creation command.A column subject to a UNIQUE constraint may be used as a foreign key. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Email Address Sign up There was an error. Please try again. You're in! Thanks for signing up. There was an error. Please try again. Thank you for signing up. Tell us why! Other Not enough details Hard to understand Submit