CHECK and DEFAULT

CHECK and DEFAULT

CHECK constraint

CHECK makes sure values in a column satisfy a condition.

CREATE TABLE Persons (
  ID int NOT NULL,
  Age int CHECK (Age >= 18)
);

Now Age must be 18 or more.

CHECK with a name

CREATE TABLE Persons (
  ID int NOT NULL,
  Age int,
  CONSTRAINT CHK_Age CHECK (Age >= 18 AND City = 'Sandnes')
);

Add a CHECK later

ALTER TABLE Persons
ADD CONSTRAINT CHK_Age CHECK (Age >= 18);

Drop a CHECK

ALTER TABLE Persons
DROP CONSTRAINT CHK_Age;    -- SQL Server

DEFAULT constraint

DEFAULT supplies a value when no value is given.

CREATE TABLE Persons (
  City varchar(255) DEFAULT 'Sandnes'
);

Inserting a row without a City stores 'Sandnes'.

Other DEFAULT examples

CREATE TABLE Orders (
  OrderDate date DEFAULT CURRENT_DATE,
  Quantity int DEFAULT 1
);

TL;DR

• CHECK enforces a condition on values.
• DEFAULT fills in a value when none is provided.
• Both can be added or removed with ALTER TABLE.