SQL UNIQUE Key

SQL UNIQUE Constraint

The SQL UNIQUE constraint is used to ensure that all values in a column or a set of columns are distinct. This command is essential for maintaining data integrity by preventing duplicate values in specified columns.

Syntax

-- To create a unique constraint when creating a table
CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
    CONSTRAINT constraint_name UNIQUE (column_name1, column_name2, ...)
);

-- To add a unique constraint to an existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name1, column_name2, ...);
  • CREATE TABLE: This is the SQL keyword used to create a new table.
  • ALTER TABLE: This is the SQL keyword used to modify an existing table.
  • UNIQUE: This is the SQL keyword used to define a unique constraint.
  • CONSTRAINT constraint_name: This specifies the name of the unique constraint.
  • column1, column2, ...: These specify the names of the columns in the table.
  • datatype: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.
  • table_name: This specifies the name of the table to create or modify.
  • column_name1, column_name2, ...: These specify the names of the columns to set as unique.

Example

Let's go through a complete example that includes creating a database, creating a table with a unique constraint, and inserting data into the table.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table with a Unique Constraint

In this step, we create a table named employees within the previously created database, defining the email column as unique.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The email column is set as unique, ensuring no duplicate email addresses are allowed.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01');

Here, we insert four rows of data into the employees table. Each record has a unique email address due to the unique constraint on the email column.

Step 4: Adding a Unique Constraint to an Existing Table

This step involves adding a unique constraint to an existing table named departments that does not have a unique constraint defined.

ALTER TABLE departments
ADD CONSTRAINT unique_department_name UNIQUE (department_name);

This command adds a unique constraint to the department_name column of the departments table, ensuring no duplicate department names are allowed.