Unique Key In MySQL

In MySQL, a unique key constraint ensures that the values in a specified column or a set of columns are unique across all rows in a table.

Unlike a primary key, a unique key allows for one null value.

Each unique key creates a unique index on the specified columns, speeding up the retrieval of data when searching based on those columns.

Unique Key Syntax In MySQL Table:

CREATE TABLE tableName (
    columnName1 data_type UNIQUE,
    columnName2 data_type,
    columnName3 data_type PRIMARY KEY,
);

Unique Key Example:

In the below command, we are creating a table named `products` using the CREATE TABLE Command.

Example:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    product_code VARCHAR(100) UNIQUE NOT NULL,
    product_price INT NOT NULL,,
    product_description VARCHAR(50) NOT NULL
);

The `products` table is created with columns `product_id`, `product_name`, `product_price`, `product_description` and `product_code`.
The `product_id` column is the primary key in the table.
The `product_code` column is declared as a unique key constraint using the `UNIQUE` keyword, ensuring that each `product_code` in the table must be unique value.
Other fields such as "product_name", "product_price" and "product_description" declared as NOT NULL values.

Create Composite UNIQUE KEY In MySQL Table:

Now, we are creating a unique key constraint on multiple columns or creating a `composite` unique key.

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    userId INT,
    userName VARCHAR(50),
    UNIQUE (userId, order_id)
);

The orders table has columns `order_id`, `userId`, and `userName`.
The combination of `userId` and `order_id` is specified as a composite unique key using the UNIQUE keyword.

Add UNIQUE Constraint to Existing MySQL Table:

we can add a UNIQUE key to the existing table using the ALTER TABLE Command.

Example:

ALTER TABLE products
ADD CONSTRAINT product_code UNIQUE (product_code);

ADD Data Into MySQL Table:

Insert Data with NULL Value On UNIQUE KEY In MySQL Table:

The below command will fail because the unique key value cannot be a NULL value.

Example:

INSERT INTO products 
(product_id, product_name, product_description, product_code, product_price)
VALUES 
(1, 'Ipad', 'Newly Arrival Smart iPad', NULL, 400);

Adding Correct Data on Unique Key In MySQL Table:

The below command will succeed because we have added a unique value in the Unique Key.

Example:

INSERT INTO products 
(product_id, product_name, product_description, product_code, product_price)
VALUES 
(2, 'Ipad', 'Newly Arrival Smart iPad', 101, 400);

Adding DUPLICATE Data on Unique Key In MySQL Table:

The below command will fail because we are adding a duplicate value in the UNIQUE Key.

Example:

INSERT INTO products 
(product_id, product_name, product_description, product_code, product_price)
VALUES 
(2, 'Ipad', 'Newly Arrival Smart iPad', 101, 400);

Note: while we attempt to insert a row with a duplicate `product_code` will result in a unique key constraint violation error.

<-- Foreign Key

Default Key -->