How to Create a Unique Constraint Across Multiple Columns in MySQL?

Specifying Unique Constraints for Multiple Columns in MySQL

In data management, ensuring data integrity is crucial for maintaining accurate and reliable records. One way to achieve this is by defining unique constraints on specific columns, prohibiting duplicate entries with identical values. In this article, we will explore how to create a unique constraint for multiple columns in MySQL.

To illustrate this concept, let's consider a votes table:

CREATE TABLE votes (
    id INT NOT NULL AUTO_INCREMENT,
    user VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    PRIMARY KEY (id)
);

This table includes an id column as the primary key, and three other columns: user, email, and address. Now, we want to ensure that no two rows in this table can have identical combinations of user, email, and address values. In other words, we want to create a unique constraint across these three columns.

To achieve this, we utilize the ALTER TABLE statement in conjunction with the ADD UNIQUE clause:

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

This command accomplishes two things:

1. Modifies the schema of the votes table using ALTER TABLE.
2. Adds a unique constraint named unique_index to the specified columns (user, email, address) using ADD UNIQUE.

This unique constraint enforces the following rule: any two rows in the votes table cannot have the same combination of user, email, and address values. This helps ensure data integrity and prevents duplicate entries from being inserted into the table.

In summary, by applying a unique constraint to multiple columns in MySQL, we can effectively maintain data integrity and ensure that each row is uniquely identified by the specified column combination.

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