Description

The SQL keyword UNIQUE is a constraint used to define a column (or a group of columns) that accepts unique values on a table.

  • A unique key can consist of one or more columns from a table.

CREATE TABLE With UNIQUE

The below SQL creates a new table, with the "user_id" column as a UNIQUE constraint.

-- Syntax on MySQL database system
CREATE TABLE users (
    user_id INT NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    age INT,
    UNIQUE (user_id)
);
-- Syntax on SQL Server or Oracle or MS Access database systems
CREATE TABLE users (
    user_id INT NOT NULL UNIQUE,
    user_name VARCHAR(255) NOT NULL,
    age INT
);

If we want to define a name to UNIQUE constraint or need to include multiple columns, then use the below syntax.

-- Syntax works for MySQL, SQL Server, Oracle, and MS Access database systems
CREATE TABLE users (
    user_id INT NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    age INT,
    CONSTRAINT UC_User UNIQUE (user_id, user_name)
);

ALTER TABLE With UNIQUE

The below SQL alters an existing table definition to set the "user_id" column as a UNIQUE constraint.

ALTER TABLE users
ADD UNIQUE (user_id);

The below SQL alters an existing table definition to set the combination of "user_id" and "user_name" columns as a UNIQUE constraint.

ALTER TABLE users
ADD CONSTRAINT UC_User UNIQUE (user_id, user_name);

DROP UNIQUE Constraint

The below SQL deletes an existing UNIQUE constraint from a table.

-- Syntax for MySQL database system
ALTER TABLE users
DROP INDEX UC_User;
-- Syntax for SQL Server, Oracle, or MS Access database systems
ALTER TABLE users
DROP CONSTRAINT UC_User;

Related Links