MySQL - Unique Constraints

In MySQL, a UNIQUE constraint is used to ensure that a column or group of columns in a table contains unique values. This means that no two rows in the table can have the same values in the specified column(s).

To create a UNIQUE constraint in MySQL, you can use the following syntax:

ALTER TABLE table_name
ADD UNIQUE (column1, column2, ...);

This will add a UNIQUE constraint to the specified columns in the table. If you want to add a UNIQUE constraint to an existing table column, you can use the following syntax:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

In this case, you need to specify a constraint name for the UNIQUE constraint.

When a UNIQUE constraint is added to a table, MySQL will automatically create an index on the specified columns. This index will ensure that the values in the specified column(s) are unique and will improve query performance for operations that involve those columns.

If you try to insert a row into a table that violates the UNIQUE constraint, MySQL will generate an error and the insertion will fail. Similarly, if you try to update a row in a way that violates the UNIQUE constraint, MySQL will generate an error and the update will fail.

Difference between primary key and unique

• Both primary key and unique constraints are used to enforce uniqueness of values in a column or group of columns in a database table, but they have some important differences.
• A primary key is a special type of unique constraint that uniquely identifies each row in a table. In other words, it is a column or a combination of columns that must contain unique values for each row in the table. A table can have only one primary key, and the primary key column(s) cannot contain NULL values. A primary key is automatically indexed and used as a reference for foreign keys in other tables.
• A unique constraint, on the other hand, ensures that the values in a column or a combination of columns are unique, but it does not have the same requirement as a primary key to uniquely identify each row in a table. A table can have multiple unique constraints, and the unique constraint column(s) can contain NULL values, as long as each NULL value is considered unique.
• In practical terms, primary keys are used to enforce entity integrity, while unique constraints are used to enforce domain integrity. Entity integrity refers to the uniqueness of each row in a table, while domain integrity refers to the uniqueness of the values in a column or a combination of columns.