mysql

Add unique constraint MySQL

UNIQUE constraint on a column ensures that no duplicate field value will be inserted into that column

-- While creating table (Single)
CREATE TABLE table_name( colum_1 data_type,  column_2 data_type UNIQUE );

-- While creating table (Combine multiple column)
CREATE TABLE table_name(
   column_1 data_type,
   column_2 data_type,
   UNIQUE(column_1, column_2)
);

-- Add unique constraints on existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE (column_name);

You can add unique constraints on a single table column or on the combination of multiple columns. You can also add UNIQUE constraints on multiple columns of the same table. Below is the example where we are adding UNIQUE constraints on two columns.

CREATE TABLE students (
    id INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(10) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT uc_email UNIQUE (email)
);

Remove UNIQUE constraints from column

You can remove the UNIQUE constraint using a drop or alter table statement

Using the DROP INDEX statement

DROP INDEX index_name ON table_name;

Using the ALTER TABLE statement

ALTER TABLE table_name
DROP INDEX index_name;

Was this helpful?