SQL UNIQUE Constraint

A UNIQUE constraint automatically creates a corresponding unique index.

Create UNIQUE Constraints

CREATE TABLE STUDENTS (
   ID INT   NOT NULL UNIQUE,
   FULL_NAME VARCHAR (200)    NOT NULL,
   ADDRESS VARCHAR (200),
   CITY VARCHAR (200),
   COUNTRY VARCHAR (200)    NOT NULL
);

MySQL UNIQUE Constraint Syntax

CREATE TABLE STUDENTS (
   ID INT   NOT NULL,
   FULL_NAME VARCHAR (200)    NOT NULL,
   ADDRESS VARCHAR (200),
   CITY VARCHAR (200),
   COUNTRY VARCHAR (200)    NOT NULL
   UNIQUE (ID)
);

Why Use UNIQUE Constraints?

A UNIQUE & A PRIMARY KEY constraint both enforce uniqueness, but there are times that a UNIQUE constraint is the better choice.

• Use a UNIQUE constraint when you want multiple constraints to a table.
• We can only attach one PRIMARY KEY to a table.
• Use a UNIQUE constraint when a column permits null values.
• PRIMARY KEY constraints can only be attached to columns that don't permit null values.