UNIQUE

MySQL

Example

CREATE TABLE Employees (
    ID INT UNIQUE,
    Name VARCHAR(255)
);

INSERT INTO Employees (ID, Name) VALUES (1, 'John');
INSERT INTO Employees (ID, Name) VALUES (1, 'David');

Output

ERROR 1062: Duplicate entry '1' for key 'ID'

Explanation

In the given example, the SQL UNIQUE constraint is used to prevent two entries from having the same ID in the ‘Employees’ table. Duplicate insertion for ID ‘1’ is attempted and results in the displayed error, since UNIQUE constraint ensures the uniqueness of the data in a column.

PostgreSQL

Example

CREATE TABLE Employees (
    ID int,
    Name varchar(255),
    Dept varchar(255),
    UNIQUE(ID)
);

Output

Table "public.Employees"
 Column |       Type      | Collation | Nullable | Default
--------+-----------------+-----------+----------+---------
 ID     | integer         |           |          |
 Name   | character(255)  |           |          |
 Dept   | character(255)  |           |          |

Explanation

In this example, a table named Employees is created with columns ID, Name, and Dept. The UNIQUE constraint is added on ID ensuring that no two records will have identical ID values within this table. This enforcement of uniqueness prevents duplicate entries for the employee’s ID. If an attempt is made to insert a duplicate ID, the database will stop the action and throw an error. This can help to maintain the integrity of the data within the table.

SQL Server

Example

CREATE TABLE Employees (
   Emp_ID INT,
   Emp_Name VARCHAR(255),
   Emp_Address VARCHAR(255),
   CONSTRAINT UC_Employees UNIQUE (Emp_ID, Emp_Name)
);

Output

The table ‘Employees’ has been created. No data output is expected from a CREATE TABLE statement in SQL Server.

Explanation

In this example, theUNIQUE keyword is used to create a unique constraint for the Emp_ID and Emp_Name fields in the Employees table. This constraint ensures that no two records have the same combination of Emp_ID and Emp_Name values, thereby enforcing data integrity in the Employees table.

Oracle

Example

CREATE TABLE Employees (
    ID INT,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Age INT,
    UNIQUE(ID)
);

INSERT INTO Employees (ID, FirstName, LastName, Age)
VALUES (1, 'John', 'Doe', 26),
       (2, 'Alice', 'Johnson', 30),
       (3, 'Bob', 'Smith', 32),
       (4, 'Charlie', 'Brown', 28);

INSERT INTO Employees (ID, FirstName, LastName, Age)
VALUES (4, 'Emily', 'Clark', 34);

Output

ORA-00001: unique constraint (System.DWW1B41MHTK8XMFS7OBGPE7LY.PK_DWW1BVAUTORW_LDW3Q)
violated

Explanation

In the example above, a table called Employees is created with a unique constraint on the ID field. As a result, when an attempt is made to insert a new record with an ID that already exists in the table (in this case, ID 4), an ORA-00001: unique constraint violated error is displayed. The unique constraint ensures that no two records in the table have the same ID value.

SQLite

Example

CREATE TABLE Employees (
   ID INT,
   Name TEXT,
   Email TEXT UNIQUE
);

INSERT INTO Employees (ID, Name, Email) VALUES (1, 'John', 'john@example.com');
INSERT INTO Employees (ID, Name, Email) VALUES (2, 'Jane', 'jane@example.com');
INSERT INTO Employees (ID, Name, Email) VALUES (3, 'Mark', 'john@example.com');

Output

Error: UNIQUE constraint failed: Employees.Email

Explanation

The UNIQUE keyword in SQLite enforces the uniqueness of the values in a column. No duplicate entries are permitted in the column where the UNIQUE keyword is included. In the example, when trying to insert a duplicate email address for a different employee, SQLite shows an error because of the UNIQUE constraint on the Email column.