MySQL UNIQUE Constraint

MySQL UNIQUE constraint ensures that the values in a column or group of columns remain unique, preventing duplicate entries in a column and maintaining the integrity of the table.

UNIQUE Constraint in MySQL

A UNIQUE constraint in MySQL prevents two records from having identical values in a column. A UNIQUE constraint can contain null values as long as the combination of values is unique. This makes it different from PRIMARY KEY as the primary key constraint can not contain null values.

There can be multiple UNIQUE constraints in a table, but only one PRIMARY KEY constraint.

The UNIQUE constraint in MySQL is like a gatekeeper that ensures that data entered into a column or group of columns remains distinct. If a duplicate value is entered, MySQL will raise an error ensuring that each value in a specified column is unique.

For example, It can be used in columns containing email addresses, ID numbers, or product codes ensuring data integrity.

UNIQUE Constraint Syntax

The syntax for using UNIQUE constraints in MySQL can vary depending on whether you are adding constraints during creation or while updating the table. You can use the following syntaxes depending on the situation.

UNIQUE Constraint on CREATE TABLE

To add a UNIQUE Constraint on the CREATE TABLE statement, use the following syntax:

CREATE TABLE table_name (
  column_name data_type UNIQUE,
  ...
);

Syntax to add UNIQUE constraint on multiple columns:

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

UNIQUE Constraint on ALTER TABLE

To add UNIQUE constraint while updating the table, use the following syntax:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

MySQL UNIQUE Constraint Example

Suppose we have a table named products with a product_code column where we initially set a UNIQUE constraint to ensure each product code is unique:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(20) UNIQUE,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

This would prevent two product codes from having the same code, maintaining the table’s integrity.

Displaying the table structure:

Inserting Some data into the product table:

product Table

Explanation: In the products table each row represents a product with a unique product code, a product name, and a price. The product_code column is constrained to be unique, so these entries will only be accepted if the product codes are distinct. If we try to insert the duplicate value in the product_code column it will give an error(Duplicate entry for key ‘products.product_code’).

DROP the UNIQUE Constraint

To remove the UNIQUE constraint, we need to use a simple command:

ALTER TABLE products
DROP INDEX product_code;

After removing the UNIQUE constraint table structure will be:

Explanation: The DROP INDEX statement removes the UNIQUE constraint from the product_code column and it allows the possibility of having duplicate product codes in the column. This might impact the data consistency of the table.

Adding a Unique Key with ALTER TABLE

Using the ALTER TABLE statement, you can add a UNIQUE constraint to an existing table.

ALTER TABLE products
ADD CONSTRAINT unique_product_code UNIQUE (product_code);

After adding a Unique constraint with ALTER TABLE table structure will be.

Explanation: This ALTER TABLE statement adds a new UNIQUE constraint named unique_product_code to the product_code column within the products table. This constraint ensures that each product code remains unique, preventing duplicates from being inserted.

Conclusion

The UNIQUE constraint in MySQL ensures of data integrity within databases, ensuring that specific columns or combinations of columns contain unique values. By preventing duplicates, it maintains accuracy and reliability in the stored information.

MySQL UNIQUE constraint is different from PRIMARY KEY constraint as, primary key can not have null values and can only be used once in a table.

In this article, we have covered how to use UNIQUE constraint with CREATE TABLE and ALTER TABLE statements. We also checked how to remove unique constraint from a table with easy examples and their explanations.