{"id":559,"date":"2016-04-15T04:28:42","date_gmt":"2016-04-15T04:28:42","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=559"},"modified":"2025-02-08T01:07:58","modified_gmt":"2025-02-08T08:07:58","slug":"sql-unique-constraint","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-unique-constraint\/","title":{"rendered":"SQL UNIQUE Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL UNIQUE<\/code> constraint to ensure all values in a column or set of columns are distinct.<\/p>\n\n\n\n

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

In SQL, a UNIQUE<\/code> constraint ensures that all values in a column or set of columns are unique within the same table. A UNIQUE<\/code> constraint helps you maintain the data integrity by preventing duplicate values in the specified columns.<\/p>\n\n\n\n

In practice, you’ll find UNIQUE<\/code> constraints helpful for defining columns that require unique values like username<\/code> and email<\/code>.<\/p>\n\n\n\n

Creating a UNIQUE constraint #<\/a><\/h2>\n\n\n\n

To create a unique constraint for a column, you use the following syntax:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name (\n    column1 datatype UNIQUE<\/span>,\n    ...\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If a UNIQUE<\/code> constraint includes more than one column, you can define a UNIQUE<\/code> constraint as a table constraint:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name (\n    column1 datatype,\n    column2 datatype,\n    UNIQUE<\/span> (column1, column2)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you provide a comma-separated list of columns in within the parentheses followed by the UNIQUE<\/code> keyword that appears at the end of the columns list.<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> headhunters (\n  id<\/span> INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/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>) NOT<\/span> NULL<\/span> UNIQUE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the headhunters<\/code> table, we use a UNIQUE<\/code> constraint for the email<\/code> column to ensure that the email column will not have any duplicate values.<\/p>\n\n\n\n
Second, insert a new row<\/a> into the headhunters<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  headhunters (first_name, last_name, email)\nVALUES<\/span>\n  ('John'<\/span>, 'Doe'<\/span>, 'j.doe@example.com'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, attempt to insert a new row with an email that already exists in the email<\/code> column:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  headhunters (first_name, last_name, email)\nVALUES<\/span>\n  ('Jane'<\/span>, 'Doe'<\/span>, 'j.doe@example.com'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The database system issued an error indicating that the email is duplicated and rejected the insert:<\/p>\n\n\n
ERROR:  duplicate key value violates unique constraint \"headhunters_email_key\"<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
UNIQUE constraint name #<\/a><\/h2>\n\n\n\n
When defining a unique constraint, you can optionally assign it a name using the CONSTRAINT<\/code> clause:<\/p>\n\n\n
CONSTRAINT constraint_name\nUNIQUE (column1, column2, ...);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement creates a table called users<\/code> that stores the user information:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> users<\/span> (\n  id<\/span> INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  username VARCHAR<\/span>(25<\/span>) NOT<\/span> NULL<\/span>,\n  password<\/span> TEXT<\/span> NOT<\/span> NULL<\/span>,\n  phone VARCHAR<\/span>(20<\/span>),\n  CONSTRAINT<\/span> unique_username UNIQUE<\/span> (username)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we create a unique constraint with the name unique_username<\/code> to ensure that the username column has no duplicate values.<\/p>\n\n\n\n
If you don\u2019t provide a name for the UNIQUE<\/code> constraint, the database system will generate a default name for it. The generated unique constraint name may vary depending on the database systems.<\/p>\n\n\n\n
The following table illustrates the default names for the unique constraints in different database systems:<\/p>\n\n\n\n
Database System<\/th>Default Unique Constraint Name<\/th>Explanation<\/th><\/tr><\/thead>
SQL Server<\/td>UQ_TableName<\/code>_ColumnName<\/code>_Hex<\/code><\/td>Prefix UQ_<\/code>, followed by the table name, column name, and a hexadecimal value to ensure uniqueness.<\/td><\/tr>
Oracle<\/td>SYS_Cn<\/code><\/td>Prefix SYS_C<\/code> and followed by a unique number (n).<\/td><\/tr>
MySQL<\/td>unique_constraint_name<\/code><\/td>Uses the name provided by the user or defaults to a system-generated name if not specified.<\/td><\/tr>
PostgreSQL<\/td>table<\/code>_column<\/code>_key<\/td>Concatenate table name, column name, and literal string key.<\/td><\/tr>
SQLite<\/td>sqlite_autoindex_TableName<\/code>_N<\/code><\/td>Prefix sqlite_autoindex_<\/code>followed by the table name and a unique number (N<\/code>)<\/td><\/tr>
DB2<\/td>SQLnnnnn<\/code><\/td>Prefix SQL, followed by a unique number nnnnn<\/code> .<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Adding UNIQUE constraints to existing tables #<\/a><\/h2>\n\n\n\n
To add a unique constraint to an existing table, you can use the ALTER TABLE ... ADD CONSTRAINT<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nADD<\/span> CONSTRAINT<\/span> constraint_name \nUNIQUE<\/span> (colum1, column2, ...);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the table has data, the values in columns (column1<\/code>, column2<\/code>, …) of the unique constraint must be distinct or you’ll encounter an error.<\/p>\n\n\n\n
For example, the following statement uses the ALTER TABLE ... ADD CONSTRAINT<\/code> statement to add a unique constraint to the users<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> users<\/span>\nADD<\/span> CONSTRAINT<\/span> unique_phone UNIQUE<\/span> (phone);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to add a new column with a UNIQUE<\/code> constraint, you use the ALTER TABLE ... ADD column<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nADD<\/span> new_column datatype UNIQUE<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement adds the employee_id<\/code> column with the UNIQUE<\/code> constraint to the users<\/code> table.<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> users<\/span>\nADD<\/span> employee_id INT<\/span> UNIQUE<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This unique constraint ensures that no two employees share the same user account.<\/p>\n\n\n\n
Removing UNIQUE constraints #<\/a><\/h2>\n\n\n\n
To remove a UNIQUE<\/code> constraint from a table, you use the ALTER TABLE .. DROP CONSTRAINT<\/code> statement with the following syntax:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nDROP<\/span> CONSTRAINT<\/span> unique_constraint_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
First, create a table<\/a> called headhunters<\/code> that store the information about headhunters:<\/p>\n\n\n
In this syntax, you use the UNIQUE<\/code> keyword to create a unique constraint for the column1<\/code> when creating the table<\/a>. This UNIQUE<\/code> constraint is a column constraint because we define it in the column definition of the table.<\/p>\n\n\n\n