NOT NULL and UNIQUE

NOT NULL and UNIQUE

NOT NULL

NOT NULL means the column must always have a value - empty values are rejected.

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

Without NOT NULL, a column is allowed to be NULL by default.

UNIQUE

UNIQUE means every value in the column must be different.

CREATE TABLE Persons (
  ID int NOT NULL,
  Email varchar(255) UNIQUE
);

UNIQUE on multiple columns

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

The pair (FirstName, LastName) must be unique.

Add them later

ALTER TABLE Persons
ADD UNIQUE (Email);

ALTER TABLE Persons
MODIFY LastName varchar(255) NOT NULL;   -- MySQL

UNIQUE vs PRIMARY KEY

• UNIQUE allows one NULL; PRIMARY KEY allows none.
• A table can have several UNIQUE columns but only one primary key.

TL;DR

• NOT NULL forbids empty values.
• UNIQUE forbids duplicate values.
• Both can be added with ALTER TABLE.
• UNIQUE can have several per table; PRIMARY KEY only one.