Unique Key - SQL

In SQL, a unique key is a constraint that ensures the values in one or more columns of a table are unique across all rows. Similar to a primary key, a unique key constraint prevents duplicate values within the specified column(s), but unlike a primary key, it allows for NULL values. However, if a column with a unique key constraint allows NULL values, it can only have one NULL value because NULL is not considered equal to any other value, including other NULLs.

Key Characteristics of Unique Keys

  • Uniqueness: Each value in the unique key column(s) must be unique within the table. However, unlike primary keys, unique key columns can contain NULL values (except in cases where NULL values are disallowed).
  • Optional: Unique key constraints are not mandatory for a table like primary keys, but they can be defined to enforce data integrity and prevent duplicate entries in specific columns.

Syntax for Defining Unique Keys

Unique keys can be defined during table creation or by altering an existing table using the UNIQUE keyword.

Inline Definition 
CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);
Separate Definition 
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1);

Example

Consider a table named "students" with columns "student_id" and "email". To ensure that each student has a unique email address, you can define the "email" column as a unique key:

sql 
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    ...
);

In this example, the unique key constraint on the "email" column ensures that each email address stored in the "students" table is unique.

Benefits of Unique Keys

  • Data Integrity: Unique keys ensure the uniqueness and integrity of data within a specific column or combination of columns, preventing duplicate entries.
  • Query Optimization: Like primary keys, unique keys are often automatically indexed by the database system, which can improve query performance for data retrieval and manipulation.
  • Flexibility: Unique keys provide a more flexible alternative to primary keys, allowing for uniqueness constraints without the restriction of disallowing NULL values.

Conclusion

In SQL, unique keys are constraints used to enforce uniqueness within specific columns or combinations of columns in a table. By preventing duplicate entries and ensuring data integrity, unique keys play a crucial role in database design and maintenance, offering flexibility and efficiency in managing data uniqueness constraints.

Next Article ❯