UNIQUE
UNIQUE is a constraint in SQL that ensures all values in a column are different, prohibiting duplicate entries in the specified column within the table.
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.
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.
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.
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.
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.