How to Enforce Uniqueness Across Multiple Columns in MySQL?

Specifying Unique Constraints for Multiple Columns in MySQL

In MySQL, you can create a unique constraint on a table to ensure that no two rows have identical values for a specified set of columns. This is useful for maintaining data integrity and enforcing uniqueness across different records.

Consider the following table:

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

To create a unique constraint that applies to the user, email, and address columns, you can use the following syntax:

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

The ALTER TABLE statement is used to modify the schema of the existing table. The ADD UNIQUE clause creates a new unique constraint named unique_index. The columns specified within the parentheses are those that will be included in the constraint.

After executing this statement, the MySQL database will enforce the unique constraint on the specified columns. This means that any attempt to insert a new row with identical values for user, email, and address will result in a SQL error and the row will not be added to the table.

By implementing unique constraints, you can ensure the integrity and accuracy of your data by prohibiting duplicate values across designated columns.

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