CHECK and DEFAULT

CHECK makes sure values in a column satisfy a condition.

sql

### CREATE TABLE Persons (

### ID int NOT NULL,

### Age int CHECK (Age >= 18)
);

Now Age must be 18 or more.

CHECK with a name

sql

### CREATE TABLE Persons (

### ID int NOT NULL,

### Age int,
  CONSTRAINT CHK_Age CHECK (Age >= 18 AND City = 'Sandnes')
);

Add a CHECK later

sql

### ALTER TABLE Persons

### ADD CONSTRAINT CHK_Age CHECK (Age >= 18);

Drop a CHECK

sql

### ALTER TABLE Persons

### DROP CONSTRAINT CHK_Age; - SQL Server

DEFAULT constraint

DEFAULT supplies a value when no value is given.

sql

### CREATE TABLE Persons (

### City varchar(255) DEFAULT 'Sandnes'
);

Inserting a row without a City stores 'Sandnes'.

Other DEFAULT examples

sql

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