UNIQUE Constraint

What is a UNIQUE Constraint in MySQL?

A UNIQUE Constraint in MySQL is used to ensure that all values stored in a column or a combination of columns are different from each other. It prevents duplicate values from being entered into a column.

Syntax:

CREATE TABLE table_name (
    column_name data_type UNIQUE
);

Example:

Let the Student table contain three columns: Student_ID, Name, and Email, with three rows. The Email column is defined with a UNIQUE Constraint because each student must have a different email address. Therefore, the same email value cannot be inserted more than once into the Email column.

Output:

+------------+---------+-------------------+
| Student_ID | Name    | Email             |
+------------+---------+-------------------+
| 101        | Rahul   | rahul@gmail.com   |
| 102        | Amit    | amit@gmail.com    |
| 103        | Neha    | neha@gmail.com    |
+------------+---------+-------------------+

Explanation:

The column Email has a UNIQUE Constraint, and the value stored in this field must always be unique.

Because a UNIQUE Constraint is set on this column, it does not allow duplicate values.

How Do You Set a UNIQUE Constraint on a Table in MySQL?

You can set a UNIQUE Constraint on a table by using the following two methods:

1. Creating a New Table Using the CREATE TABLE Statement.

   
   

   Example:

   CREATE TABLE Student (
       Student_ID INT,
       Name VARCHAR(50),
       Email VARCHAR(100) UNIQUE
   );
   

   
   

   Steps:

   1. First, you write the CREATE TABLE statement, and then you write the table name.
   2. Open the parentheses (.
   3. Inside the parentheses, write the column names and their data types.
   4. For the column that you want to make UNIQUE, write the UNIQUE keyword after the data type of that column.
   5. Write the remaining column names with their data types, separating each field with a comma.
   6. Close the parentheses ) and end the query with a semicolon ;.
   7. Finally, execute the query to create the table with the UNIQUE Constraint.

2. By adding a UNIQUE Constraint to an existing table using the ALTER TABLE statement.

   
   

   Example:

   ALTER TABLE Student
   ADD UNIQUE (Email);
   

   
   

   Steps:

   1. First, write the ALTER TABLE statement.
   2. Next, write the name of the existing table.
   3. Write the ADD UNIQUE clause.
   4. Inside the parentheses (), write the column name that you want to make UNIQUE.
   5. Close the parentheses ) and end the query with a semicolon ;.
   6. Finally, execute the query to add the UNIQUE Constraint to the existing table.

Advantages of a UNIQUE Constraint

• Prevents Duplicate Values

  When you define a UNIQUE Constraint on a specific column, it prevents duplicate values from being entered into that column.

• Maintains Data Accuracy

  It helps maintain accurate data by making sure that important values in the column do not repeat.

• Allows Multiple Unique Columns

  A table can have multiple UNIQUE Constraints. This allows different columns to store unique values.

• Improves Data Organization

  The UNIQUE Constraint prevents duplicate values from being entered into the column on which it is set. By preventing repeated values in specific columns, it helps organize data properly.

Common Mistakes When Setting a UNIQUE Constraint in MySQL

The following are common mistakes that you should avoid when setting the Constraints.

1. To Insert Duplicate Values

   You should not insert duplicate values into a column with a UNIQUE Constraint because it does not permit repeated values.

2. To Apply UNIQUE Constraint on the Wrong Column

   You should choose the correct column for a UNIQUE Constraint because the values of that column should be different for each record.

3. To Confuse UNIQUE Constraint with Primary Key

   You should not confuse between Primary Key and UNIQUE Key because when you define a UNIQUE Constraint, it contains the feature that allows more columns to individually keep unique values, and a Primary Key does not permit this feature.

4. To Ignore NULL Values

   You should understand that a UNIQUE Constraint allows NULL values, but it does not permit duplicate values to be entered into the column.

5. Choose the Incorrect Data Type

   You should choose the correct data type for the UNIQUE column because it should store the required values properly.

6. To Create Unnecessary UNIQUE Constraints

   You should not create unnecessary UNIQUE Constraints for more columns because they can make database design more complex.

What is a Composite UNIQUE Constraint and Why is it used?

A Composite UNIQUE Constraint is set on two or more columns in a table. It ensures that the combined values of these columns are unique for every record in the table.

It is used when a single column cannot ensure the uniqueness of the required data. In such cases, two or more columns whose combined values must be unique are used together to create a Composite UNIQUE Constraint.

Syntax:

CREATE TABLE table_name
(
    column1 datatype,
    column2 datatype,
    column3 datatype,

    UNIQUE (column1, column2)
);

Here,

➤ table_name → Specifies the name of the table.

➤ UNIQUE (column1, column2) → column1 and column2 are the columns that together form the Composite UNIQUE Constraint.

Example:

The following example creates a Student table. In this table, both columns (First_Name and Last_Name) are combined to create a Composite UNIQUE Constraint. This ensures that the combined values of these two columns are unique for each record in the table.

CREATE TABLE Student ( First_Name VARCHAR(50),
                       Last_Name VARCHAR(50),
                       Age INT,
                       UNIQUE (First_Name, Last_Name) );

Output:

After executing the above query, the Student table is created successfully.

The following table shows how records can be stored in the Student table.

+------------+-----------+-----+
| First_Name | Last_Name | Age |
+------------+-----------+-----+
| Rahul      | Kumar     | 20  |
| Rahul      | Singh     | 21  |
| Amit       | Kumar     | 22  |
+------------+-----------+-----+

Explanation:

In the above table, First_Name and Last_Name together create the Composite UNIQUE Constraint.
Both columns combined ensure that each combination is unique.
Therefore, the same combination of First_Name and Last_Name cannot be added again in the table.

Note:

• A UNIQUE Constraint does not identify records in a table. It only ensures that duplicate values are not give in a column or a combination of columns.
• A Primary Key is used to uniquely identify each record in a table, whereas a UNIQUE Constraint is used to prevent duplicate values.