How to Specify Unique Constraints for Multiple Columns in MySQL

Today’s post will look at the methods for specifying unique constraints for multiple columns in MySQL.

Specify Unique Constraints for Multiple Columns in MySQL

You may occasionally wish to guarantee that each value in a column or a collection of columns is distinct.

For instance, user email addresses in the Employees’ database or customer phone numbers in the customers’ table should differ. A unique constraint is used to enforce this rule.

An integrity constraint called UNIQUE assures that each value in a column or a combination of columns is distinct. A table constraint or a column constraint can both be unique constraints.

Syntax:

CREATE TABLE table_name(
    column_name data_type UNIQUE,
);

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

Column constraints are used in this UNIQUE constraint. Additionally, it may be used to enforce the unique rule for a single column.

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

Syntax:

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

The MySQL ALTER TABLE command can be used to add, modify, or drop/delete columns from a table. The ADD UNIQUE command can add a unique constraint if the column already exists without one.

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

Syntax:

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 concept, 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 that came before, we created a database called Employees with 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 designate an email as a unique column using the keyword UNIQUE.

Like the first, we created an EmployeeDepartment table containing the variables email and department. An error will be generated if you insert or change a value that duplicates data in the email+department column.

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

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

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

Query executed successfully.