Creating A MySQL Simple - Unique Index

The columns included in the simple index can hold duplicate values. To create an index that will not hold duplicate values, an index can be: Simple Unique Index.

Simple Unique index:

A unique index created on single table data column is called a Simple Unique index.
The following is the syntax for creating Simple Unique index as shown in Diagram 1.
Syntax:
<System prompt> CREATE UNIQUE INDEX <IndexName> ON <Table Name> (<Column Name>);

Example:
mysql> CREATE UNIQUE INDEX index_id ON clientmaster (Client_ID);

Diagram 1

Testing whether the index was created:

To test whether the unique index has been bound to the clientmaster table, execute the SHOW CREATE TABLE statement as shown in Diagram 2:

The following is the syntax for verifying the index created on a column(s) of a table
Syntax:
<System prompt> SHOW CREATE TABLE <Table Name> \G;

Example:
mysql> SHOW CREATE TABLE clientmaster \G;

Diagram 2

In Diagram 2, UNIQUE KEY ‘index_id’ (‘Client_ID’) indicates that the unique index is successfully created on the column ‘Client_ID’.

If you try to insert duplicate value data into the ‘Client_ID’ column, it will immediately throw an error.

• < Prev
• Next >