{"id":5275,"date":"2014-05-22T23:03:58","date_gmt":"2014-05-23T06:03:58","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5275"},"modified":"2023-12-28T05:57:25","modified_gmt":"2023-12-28T12:57:25","slug":"mysql-unique-constraint","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-unique-constraint\/","title":{"rendered":"MySQL UNIQUE Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL UNIQUE<\/code> constraint and how to use it to enforce the uniqueness of values in a column or a group of columns in a table.<\/p>\n\n\n\n

Introduction to MySQL UNIQUE constraint<\/h2>\n\n\n\n

Sometimes, you want to ensure values in a column or a group of columns are unique. For example, email addresses of users in the users<\/code> table, or phone numbers of customers in the customers<\/code> table should be unique. To enforce this rule, you use a UNIQUE<\/code> constraint.<\/p>\n\n\n\n

A UNIQUE<\/code> constraint is an integrity constraint that ensures the uniqueness of values in a column or group of columns.\u00a0A UNIQUE<\/code> constraint can be either a column constraint or a table constraint.<\/p>\n\n\n\n

To define a UNIQUE<\/code> constraint for a column when creating a table<\/a>, you use the following syntax:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name(\n    ...,\n\u00a0\u00a0\u00a0 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
In this syntax, you include the UNIQUE<\/code> keyword in the definition of the column that you want to enforce the uniqueness. <\/p>\n\n\n\n
This UNIQUE<\/code> constraint is a column constraint. And you can use it to enforce the unique rule for one column.<\/p>\n\n\n\n
To define a UNIQUE<\/code>  constraint for two or more columns, you use the following syntax:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name(\n   ...\n   column1 datatype,\n   column2 datatype,\n   ...,\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 add a comma-separated list of columns in parentheses after the UNIQUE<\/code> keyword. In this case, MySQL will use the combination of values in both columns column1<\/code> and column2<\/code> to evaluate the uniqueness.<\/p>\n\n\n\n
If you define a UNIQUE<\/code> constraint without specifying a name, MySQL automatically generates a name for it. To define a UNIQUE<\/code> constraint with a name, you use this syntax:<\/p>\n\n\n
[CONSTRAINT constraint_name]\nUNIQUE(column_list)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the name of the UNIQUE<\/code> constraint after the CONSTRAINT<\/code> keyword.<\/p>\n\n\n\n
MySQL UNIQUE constraint example<\/h2>\n\n\n\n
First, creates a new table<\/a> named suppliers<\/code> with the two UNIQUE<\/code> constraints:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> suppliers (\n    supplier_id INT<\/span> AUTO_INCREMENT,\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    phone VARCHAR<\/span>(15<\/span>) NOT<\/span> NULL<\/span> UNIQUE<\/span>,\n    address VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (supplier_id),\n    CONSTRAINT<\/span> uc_name_address UNIQUE<\/span> (name<\/span>,address)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the first UNIQUE<\/code> constraint is defined for the phone<\/code> column:<\/p>\n\n\n
phone VARCHAR(12) NOT NULL UNIQUE<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The second UNIQUE<\/code> constraint includes both name<\/code> and address<\/code> columns:<\/p>\n\n\n
CONSTRAINT uc_name_address UNIQUE (name , address)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> suppliers(name<\/span>, phone, address) \nVALUES<\/span>( 'ABC Inc'<\/span>, \n       '(408)-908-2476'<\/span>,\n       '4000 North 1st Street'<\/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 different supplier but has the phone number that already exists in the suppliers<\/code> table.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> suppliers(name<\/span>, phone, address) \nVALUES<\/span>( 'XYZ Corporation'<\/span>,'(408)-908-2476'<\/span>,'3000 North 1st Street'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued the following error:<\/p>\n\n\n
Error<\/span> Code: 1062.<\/span> Duplicate entry '(408)-908-2476'<\/span> for<\/span> key 'phone'<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, change the phone number to a different one and execute the insert statement again.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> suppliers(name<\/span>, phone, address) \nVALUES<\/span>( 'XYZ Corporation'<\/span>,'(408)-908-3333'<\/span>,'3000 North 1st Street'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, insert a row into the suppliers<\/code> table with values that already exist in the columns name<\/code> and address<\/code> :<\/p>\n\n\n
INSERT<\/span> INTO<\/span> suppliers(name<\/span>, phone, address) \nVALUES<\/span>( 'ABC Inc'<\/span>, \n       '(408)-908-1111'<\/span>,\n       '4000 North 1st Street'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued an error because the UNIQUE<\/code> constraint uc_name_address<\/code> was violated.<\/p>\n\n\n
Error<\/span> Code: 1062.<\/span> Duplicate entry 'ABC Inc-4000 North 1st Street'<\/span> for<\/span> key 'uc_name_address'<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL UNIQUE constraint & NULL<\/h2>\n\n\n\n
In MySQL, NULL values are treated as distinct when it comes to unique constraints. Therefore, if you have a column that accepts NULL values, you can insert multiple values into the column.<\/p>\n\n\n\n
First, create a new table<\/a> called contacts<\/code>:<\/p>\n\n\n
CREATE TABLE contacts(\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    name VARCHAR(255<\/span>) NOT NULL<\/span>,\n    phone VARCHAR(20<\/span>) UNIQUE\n)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The contacts table has a phone<\/code> column with a UNIQUE constraint. Also, the phone<\/code> column can accept NULL values.<\/p>\n\n\n\n
Second, insert some rows into the contacts<\/code> table:<\/p>\n\n\n
INSERT INTO contacts(name, phone)\nVALUES\n   ('Alice'<\/span>,'(408)-102-2456'<\/span>),\n   ('John'<\/span>, NULL<\/span>),\n   ('Jane'<\/span>, NULL<\/span>);   <\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we can insert two NULL values into the phone column without causing a duplicate.<\/p>\n\n\n\n
Third, retrieve data from the contacts<\/code> table:<\/p>\n\n\n
SELECT * FROM contacts;<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+-------+----------------+\r\n| id | name  | phone          |\r\n+----+-------+----------------+\r\n|  1<\/span> | Alice | (408<\/span>)-102<\/span>-2456<\/span> |\r\n|  2<\/span> | John  | NULL<\/span>           |\r\n|  3<\/span> | Jane  | NULL<\/span>           |\r\n+----+-------+----------------+\r\n3<\/span> rows in set (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL UNIQUE constraints and indexes<\/h2>\n\n\n\n
When you define a unique constraint for a column or a group of columns, MySQL creates a corresponding UNIQUE<\/code> index<\/a> and uses this index to enforce the rule.<\/p>\n\n\n\n
The SHOW CREATE TABLE<\/code> statement shows the definition of the suppliers<\/code> table:<\/p>\n\n\n
SHOW<\/span> CREATE<\/span> TABLE<\/span> suppliers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The output indicates that MySQL created two UNIQUE<\/code> indexes on the suppliers<\/code> table: phone<\/code> and uc_name_address<\/code>.<\/p>\n\n\n\n
The following SHOW INDEX<\/a><\/code> statement displays all indexes associated with the suppliers<\/code> table.<\/p>\n\n\n
SHOW<\/span> INDEX<\/span> FROM<\/span> suppliers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Drop a unique constraint<\/h2>\n\n\n\n
To drop a UNIQUE<\/code> constraint, you can use DROP INDEX<\/code><\/a> or ALTER TABLE<\/a><\/code> statement:<\/p>\n\n\n
DROP<\/span> INDEX<\/span> index_name ON<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
ALTER<\/span> TABLE<\/span> table_name\nDROP<\/span> INDEX<\/span> index_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement drops the uc_name_address<\/code> constraint on the suppliers<\/code> table:<\/p>\n\n\n
DROP<\/span> INDEX<\/span> uc_name_address ON<\/span> suppliers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Execute the SHOW INDEX<\/code> statement again to verify if the uc_name_unique<\/code> constraint has been removed.<\/p>\n\n\n
SHOW<\/span> INDEX<\/span> FROM<\/span> suppliers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Add new unique constraint<\/h2>\n\n\n\n
The following ALTER TABLE ADD CONSTRAINT<\/code> adds a unique constraint to a column of an existing table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nADD<\/span> CONSTRAINT<\/span> constraint_name \nUNIQUE<\/span> (column_list);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement adds a UNIQUE<\/code> constraint uc_name_address<\/code> back to the suppliers<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> suppliers\nADD<\/span> CONSTRAINT<\/span> uc_name_address \nUNIQUE<\/span> (name<\/span>,address);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Note that MySQL will not add a unique constraint if the existing data in the columns of specified in the unique constraint does not comply with the uniqueness rule.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use MySQL UNIQUE<\/code> constraint to enforce the uniqueness of values in a column or group of columns of a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, insert a row<\/a> into the suppliers<\/code> table:<\/p>\n\n\n
If you insert<\/a> or update<\/a> a value that causes a duplicate in the column1<\/code>, MySQL rejects the change and issues an error.<\/p>\n\n\n\n