SQL CONSTRAINT

You can place constraints to limit the type of data that can go into a table. Such constraints can be specified when the table when the table is first created via the CREATE TABLE statement, or after the table is already created via the ALTER TABLE statement.

Common types of constraints include the following:

- NOT NULL
- UNIQUE
- CHECK
- Primary Key
- Foreign Key

Each is described in detail below.

NOT NULL

By default, a column can hold NULL. If you not want to allow NULL value in a column, you will want to place a constraint on this column specifying that NULL is now not an allowable value.

For example, in the following statement,

CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30));

Columns "SID" and "Last_Name" cannot include NULL, while "First_Name" can include NULL.

UNIQUE

The UNIQUE constraint ensures that all values in a column are distinct.

For example, in the following statement,

CREATE TABLE Customer
(SID integer Unique,
Last_Name varchar (30),
First_Name varchar(30));

Column "SID" cannot include duplicate values, while such constraint does not hold for columns "Last_Name" and "First_Name".

Please note that a column that is specified as a primary key must also be unique. At the same time, a column that's unique may or may not be a primary key.

CHECK

The CHECK constraint ensures that all values in a column satisfy certain conditions.

For example, in the following statement,

CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));

Column "SID" must only include integers greater than 0.

Please note that the CHECK constraint does not get enforced by MySQL at this time.

Primary Key and Foreign Key are discussed in the next two sections.