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
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.
Common types of constraints include the following:
- NOT NULL
- UNIQUE
- CHECK
- Primary Key
- Foreign Key
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.