NOT NULL and UNIQUE

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

sql

### 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.

sql

### CREATE TABLE Persons (

### ID int NOT NULL,

### Email varchar(255) UNIQUE
);

UNIQUE on multiple columns

sql

### 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

sql

### 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.