Unique Key - (MySQL Key)

In MySQL, a unique key is a feature that ensures that every row in a table has a unique value for one or more columns. Unique keys prevent duplicate entries in a table and are a key component of database normalization. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of unique keys in MySQL.

Syntax

The syntax for creating a unique key in MySQL is as follows:

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

This creates a unique key constraint on one or more columns in a table. The constraint can be given a name (constraint_name) to make it easier to identify.

Alternatively, you can create a unique key when you create a table as part of the column definition:

CREATE TABLE table_name (
   column1 int UNIQUE,
   column2 varchar(30) UNIQUE
);

Example

Let's say we have a table called "users" that contains the columns "user_id", "username", and "email". We want to ensure that each user has a unique email address. Here's how we can implement it:

ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);

Now, when we try to insert a new row with a duplicate email address, MySQL will return an error and prevent the insertion.

Output

When we try to insert a new row with a duplicate email address after creating the unique key, MySQL will return an error message similar to this:

ERROR 1062 (23000): Duplicate entry 'test@example.com' for key 'email_unique'

This error message indicates that there is a duplicate entry for the unique key "email_unique".

Explanation

In the example above, we created a unique key constraint on the "email" column of the "users" table to ensure that each user has a unique email address. If we try to insert a new row with a duplicate email address, MySQL will return an error and prevent the insertion.

Use

Unique keys are useful for ensuring that data in a table is unique and for preventing duplicate entries. They are a key component of database normalization and can help ensure data consistency and accuracy.

Important Points

• Unique keys can be created using the ALTER TABLE statement or as part of the CREATE TABLE statement.
• A unique key constraint can encompass multiple columns.
• Unique key constraints can be given a name to make them easier to identify in the event of an error.
• Unique keys are a key component of database normalization and can help ensure data consistency and accuracy.

Summary

In this tutorial, we discussed the syntax, example, output, explanation, use, and important points of unique keys in MySQL. Unique keys are a key component of database normalization and can help ensure data consistency and accuracy by preventing duplicate entries in a table. Understanding how to create and use unique keys can help you build more robust and reliable databases.