JIYIK CN >

This article will explore the methods of specifying unique constraints for multiple columns in MySQL.

Specifying unique constraints for multiple columns in MySQL

You may sometimes want to guarantee that every value in a column or set of columns is distinct.

For example, user email addresses in an employee database or customer phone numbers in a customer table should be different. A unique constraint is used to enforce this rule.

An integrity constraint called UNIQUE ensures that each value in a column or combination of columns is distinct. Either a table constraint or a column constraint can be a unique constraint.

grammar:

CREATE TABLE table_name(
    column_name data_type UNIQUE,
);

In the above syntax, the column specification for which you wish to impose a uniqueness rule includes the UNIQUE keyword. If you insert or update a value that results in a duplicate column name, MySQL rejects the modification and generates an error.

Column constraints are used in this UNIQUE constraint. Also, it can be used to enforce unique rules for a single column.

The following syntax is used to create a UNIQUE constraint for two or more columns:

grammar:

CREATE TABLE table_name(
   column_name1 data_type,
   column_name2 data_type,
   UNIQUE(column_name1,column_name2)
);

MySQL ALTER TABLE command can be used to add, modify, or delete/remove columns in a table. If the column already exists but does not have a unique constraint, then the ADD UNIQUE command can add a unique constraint.

Use the following syntax to create a unique constraint for two or more columns.

grammar:

ALTER TABLE table_name ADD UNIQUE column_name;
ALTER TABLE table_name ADD UNIQUE `index_name`(column_name1, column_name2);

To further understand the previous concepts, consider the following example:

CREATE TABLE Employees(
    email varchar(255) UNIQUE,
    first_Name VARCHAR(255),
    last_Name VARCHAR(255)
);
CREATE TABLE EmployeeDepartment(
   email varchar(255),
   department varchar(255),
   UNIQUE(email,department)
);
-- If the Employees table does not have a unique email constraint
ALTER TABLE EmployeeDepartment ADD UNIQUE email;
-- If the EmployeeDepartment table does not have a unique constraint
ALTER TABLE EmployeeDepartment ADD UNIQUE `unique_department_emp`(email,department);

In the first example above, we created a database called Employees that contained the attributes email, first_Name, and last_Name. If you change or edit a value that duplicates data in the email column, an error will be generated.

We will use the keyword UNIQUEEmail to designate it as a unique column.

Like the first one, we created an EmployeeDepartment table with variables email and department. If you insert or change a value that duplicates the data in the email+department columns, an error will be generated.

We will use the keyword UNIQUE to make the combination of email and department a unique column.

You can modify an existing table by similarly adding a new unique constraint.

Run the above line of code in any browser compatible with MySQL. It will display the following result:

Query executed successfully.