Progress

MySQL UNIQUE Constraint to avoid duplicates with Examples

Introduction: Unique conatraint in MySQL is to avoid duplicate entries in a table. It forces all value of certain attribute to remain unique, for example username or email.

MySQL UNIQUE Constraint to avoid duplicates with Examples

The database has an advantage over traditional file systems that we can easily manage duplicate entries while maintaining data consistency. The UNIQUE constraint in MySQL is for the same purpose, to avoid duplicate entries in a database.

It is one of the most useful constraints while creating a database.

Introduction to UNIQUE Constraint in MySQL

In certain situations, we want only unique entries for a column or a group of columns in a database. Real-life data is unpredictive and we need some constraint to avoid duplicate entries on the database side.

The UNIQUE is an integrity constraint which makes sure that each and every value of the column is different.

Primary Key and UNIQUE constraint are the same, the difference is that a table can have only one primary key but multiple unique constraints.

A sample use case will be email and username because they need to be unique in the user's table to avoid multiple accounts with the same email.

Columns of data type Text cannot have UNIQUE constraint because it requires data type of known length.

Create a table with the UNIQUE constraint

CREATE TABLE users (
  first_name TEXT,
  last_name Text,
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(50) NOT NULL UNIQUE,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_digest TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);
email and username need to be unique

In the above query, we add the UNIQUE constraint to the email and username. It will ensure that both columns will have unique entries through the table.

MySQL will give an error if we try to insert duplicate entries on the columns with the UNIQUE constraint.

INSERT INTO users VALUES('Digvijay', 'Singh',NULL, '[email protected]', 'diggi', 'temp', '2020-01-01 10:10:10', '2020-01-01 10:10:10');
INSERT INTO users VALUES('Digvijay', 'Singh',NULL, '[email protected]', 'diggi', 'temp', '2020-01-01 10:10:10', '2020-01-01 10:10:10');

The Output was :

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'users.email'

The id attribute is the primary key of the table so it will also contain unique value throughout the table.

Here is the syntax to define the UNIQUE constraint on multiple attributes of a table.

CREATE TABLE users (
  first_name TEXT,
  last_name Text,
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(50) NOT NULL,
  username VARCHAR(50) NOT NULL,
  password_digest TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  UNIQUE(email, username)
);

This ensures the uniqueness of the values in both email and username columns combined. It means email and username can be the same individual but not combined.

mysql> INSERT INTO users1 VALUES('Digvijay', 'Singh',NULL, '[email protected]', 'diggi', 'temp', '2020-01-01 10:10:10', '2020-01-01 10:10:10');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO users1 VALUES('Digvijay', 'Singh',NULL, '[email protected]','diggi2', 'temp', '2020-01-01 10:10:10', '2020-01-01 10:10:10');
Query OK, 1 row affected (0.00 sec)



mysql>INSERT INTO users1 VALUES('Digvijay', 'Singh',NULL, '[email protected]', 'diggi', 'temp', '2020-01-01 10:10:10', '2020-01-01 10:10:10');
ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'users1.email'

Add the UNIQUE constraint to an existing table

Here is the syntax if you want to add the UNIQUE constraint to the existing table.

ALTER TABLE users ADD CONSTRAINT index_name UNIQUE(attribute);

For example, if we want to add the UNIQUE constraint to the users table.

ALTER TABLE users ADD CONSTRAINT email UNIQUE(email);
ALTER TABLE users ADD CONSTRAINT username UNIQUE(username);

Remove or DROP a UNIQUE Constraint in MySQL

DROP INDEX email ON users;
DROP INDEX username ON users;

Summary

UNIQUE is integrity constraint of MySQL which ensures that all the values of the column are unique or they DO NOT contain any duplicate values.

Here are a few things to remember about UNIQUE constraint.

  • We can have multiple UNIQUE constraints in a table but only one primary key.
  • UNIQUE cannot be applied to the TEXT data type in MySQL.
  • Inserting duplicate entries on UNIQUE constraint columns will throw an error.

Use UNIQUE only when duplicate entries are intolerable in the database. DISTINCT keyword with SELECT keyword can be helpful and related use case when the table has duplicate entries.

Share the Post ;)

Related Posts