DBMS Constraint Unique | CrackEase

Unique in DBMS

Unique

To ensure that every value in a column is distinct, use the UNIQUE constraint.
The UNIQUE constraint prevents duplicate values in the specified column(s).
If a duplicate value is inserted into a UNIQUE column, the database will raise an error and reject the insert/update.
You may apply UNIQUE constraints to one or more columns in a table (single-column unique or composite unique).

CREATE

Syntax

  
CREATE TABLE table_name (
    column1 datatype(size) UNIQUE,
    column2 datatype(size),
    column3 datatype(size)
);

Example

  
CREATE TABLE Persons (
    ID int UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

In the example above the ID column has a UNIQUE constraint — no two rows can have the same ID value.

Combine NOT NULL and UNIQUE

You can apply multiple constraints to a single column.
When you combine NOT NULL and UNIQUE, the column must contain distinct, non-NULL values.

Example

  
ID int NOT NULL UNIQUE

Primary key vs Unique key

Both enforce uniqueness, but a PRIMARY KEY additionally enforces NOT NULL and identifies the row uniquely for the table's identity.
A table can have only one PRIMARY KEY, but it can have multiple UNIQUE constraints.
UNIQUE columns typically allow a single NULL (depending on RDBMS) — behaviour varies between databases. PRIMARY KEY never allows NULL.

Primary Key VS Unique Key

Primary Key

Unique Key

Footer Content | CrackEase

→

←