How to Enforce Unique Constraints Across Multiple Columns in MySQL?

Specifying Unique Constraints for Multiple MySQL Columns

In the context of MySQL database management, a table's unique constraint ensures that no two rows contain identical values for a specified set of columns. To enforce such a constraint, we utilize the ALTER TABLE and ADD UNIQUE commands in conjunction.

Synopsis:

ALTER TABLE <table_name> ADD UNIQUE <index_name>(<column1>, <column2>, ...)

Example:

Consider the following table schema:

table votes (
    id,
    user,
    email,
    address,
    primary key(id),
);

To ensure that the combination of user, email, and address columns is unique, we can execute the following command:

ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);

Explanation:

• The ALTER TABLE statement modifies the table's schema by adding a unique constraint.
• The ADD UNIQUE clause specifies that the specified columns (user, email, address) must have unique values across all rows.
• The unique_index is a custom name assigned to the index.

By implementing this constraint, MySQL guarantees that there cannot be multiple rows with identical user, email, and address combinations, preventing duplicate entries.

The above is the detailed content of How to Enforce Unique Constraints Across Multiple Columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!