SQL Unique Key

What distinguishes the SQL Unique Key?

The SQL Unique Key acts as a guardian, preventing the entry of duplicate values in a specific column of a table. Unlike the Primary Key, it allows for the acceptance of NULL values, making it a versatile tool for maintaining uniqueness while accommodating certain data variations.

Features of SQL Unique Key

1. Uniqueness with Flexibility

The SQL Unique Key, akin to the Primary Key, enforces uniqueness in a column. However, it distinguishes itself by permitting the acceptance of a single NULL value, providing flexibility in data entry.

2. Singular Identity

A Unique Key ensures that no two records within a column share the same value. This guarantees a singular identity for each entry, contributing to a well-organized and distinct database.

3. Foreign Key Compatibility

Beyond its role as a standalone constraint, the SQL Unique Key can serve as a foreign key in another table. This interconnectivity enhances the relational aspect of databases, fostering coherence and efficient data management.

4. Multiple Unique Columns

A single table can host more than one Unique Key, allowing you to enforce uniqueness across multiple columns. This feature is particularly valuable when differentiating records based on various criteria.

Creating SQL Unique Key during table creation

Please follow below syntax to create a unique key during the table creation.

CREATE TABLE exe_table_name(
    col_one datatype UNIQUE KEY,
    col_two datatype,
    .....
    .....
    col_N datatype
);

Example

Let's consider a practical scenario. We have a table named Customer_detail, and we want to enforce uniqueness on the ID column. Here's how it's done:

CREATE TABLE CUSTOMER_DETAIL (
    ID INT NOT NULL UNIQUE KEY,
    NAME VARCHAR(20) NOT NULL,
    ADDRESS CHAR (25),
    SALARY DECIMAL (18, 2)
);

The UNIQUE KEY constraint is applied to the ID column, ensuring a distinct identifier for each customer.

Verification

INSERT INTO CUSTOMER_DETAIL VALUES
(1, 'Ramesh', 'Ahmedabad', 2000.00 ),
(1, 'Khilan', 'Delhi', 1500.00 );

If you attempt to insert duplicate values into this table, SQL will display an error like this.

Error Code: 1062. Duplicate entry '1' for key 'customer_detail.ID'	0.015 sec

Multiple Unique Keys

In situations where differentiation is important across several columns, the SQL Unique Key shines. Let's see how to implement this:

CREATE TABLE exe_table_name(
    col_one datatype UNIQUE KEY,
    col_two datatype UNIQUE KEY,,
    .....
    .....
    col_N datatype
);

Example

Consider a table named BUYERS, where uniqueness is enforced on both the ID and NAME columns:

CREATE TABLE BUYER_DETAILS (
    BUY_ID INT NOT NULL UNIQUE KEY,
    BUY_NAME VARCHAR(20) NOT NULL UNIQUE KEY,
    BUY_ADDRESS CHAR (25),
    BUY_SALARY DECIMAL (18, 2)
);

This setup ensures that no two buyers share the same ID or name, adding an extra layer of distinctiveness.

Enhancing Existing Columns with Unique Key

The journey of implementing a Unique Key doesn't end with table creation. We can augment an existing column with this constraint using the ALTER TABLE statement.

ALTER TABLE table_name ADD CONSTRAINT
UNIQUE_KEY_NAME UNIQUE (column_name);

Dropping an SQL Unique Key

In situations where the Unique Key is no longer necessary, we can remove it using the ALTER TABLE statement.

ALTER TABLE table_name DROP CONSTRAINT UNIQUE_KEY_NAME;