How to Use SQL UNIQUE in Oracle

SQL UNIQUE Constraint: Main Tips

• You can use this constraint to classify each data cell in a table of a specific database in Oracle.
• It is described in a PRIMARY KEY by default.
• There can be only one PRIMARY KEY and a lot of UNIQUE constraints per table.
• The SQL SELECT UNIQUE construct only works in Oracle. For other management systems, use SQL SELECT DISTINCT.

SQL UNIQUE Syntax Needed to CREATE TABLE

In the example below, we produce the UNIQUE constraint while creating the table:

CREATE TABLE People (
    ID int NOT NULL UNIQUE,
    NameLast varchar(255) NOT NULL,
    NameFirst varchar(255),
    Year int
);

The code example below permits naming the constraints:

CREATE TABLE People (
    ID int NOT NULL,
    LName varchar(255) NOT NULL,
    FName varchar(255),
    Year int,
    CONSTRAINT UC_Person UNIQUE (ID,LName)
);

SQL UNIQUE on ALTER TABLE: Example

In the example below, we produce the UNIQUE constraint after the table creation:

ALTER TABLE People
ADD UNIQUE (ID);

The code example below permits naming the constraints:

ALTER TABLE People
ADD CONSTRAINT UC_Person UNIQUE (ID,LName);

Note: SQL SELECT UNIQUE construct is non-standard and only supported by Oracle. For other database systems, use SQL SELECT DISTINCT.