SQL UNIQUEConstraints

UNIQUEConstraints in SQL are used to ensure that all values in one or more columns are unique, meaning that there cannot be duplicate values in the columns to which the constraint applies.

UNIQUESimilar to the PRIMARY KEY (PRIMARY KEY) constraint, butUNIQUEThe UNIQUE constraint allows values in the column to beNULL, while the PRIMARY KEY does not.

The PRIMARY KEY constraint comes with the UNIQUE constraint functionality.

Each table can have multiple UNIQUE constraints, but can only define one PRIMARY KEY constraint.

Usage Scenarios

  • Ensure uniqueness: For example, ensure fields such as email addresses, usernames, etc., are unique across the entire table.
  • Apply on multiple columns: You can createUNIQUEconstraint on multiple columns to ensure the uniqueness of combined values.

SQL UNIQUE Constraint on CREATE TABLE

When creating a table, you can define a UNIQUE constraint on a specific column or multiple columns to ensure that the values in these columns are unique within the table.

In the "Persons" table'sP_Idcolumn, addUNIQUEconstraint

MySQL：

CREATE TABLE Persons (
    P_Id INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(255),
    UNIQUE (P_Id)
);

SQL Server / Oracle / MS Access：

CREATE TABLE Persons (
    P_Id INT NOT NULL UNIQUE,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(255)
);

UNIQUEconstraint and define on multiple columns

To specify a name for the UNIQUE constraint and apply it to multiple columns, you can use the following syntax:

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons (
    P_Id INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(255),
    CONSTRAINT uc_PersonID UNIQUE (P_Id, LastName)
);

Add UNIQUE Constraint on ALTER TABLE

If the table already exists, you can use the ALTER TABLE statement to add a UNIQUE constraint to the specified column.

Add on the "P_Id" columnUNIQUEconstraint

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD UNIQUE (P_Id);

NamingUNIQUEconstraint and apply on multiple columns

To name a UNIQUE constraint, and define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id, LastName);

Drop UNIQUE Constraint

If you need to remove a UNIQUE constraint, you can use the following SQL statement:

MySQL：

ALTER TABLE Persons
DROP INDEX uc_PersonID;

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID;
Other Extensions