Back
Next
We can place constraints to limit the type of
data that can go into a table. Now in SQL Tutorial section we will see that such constraints
can be specified when the table is
first created via the SQL CREATE TABLE statement, or
after the table is already created via the SQL ALTER
TABLE statement.
Common types of constraints include the following:
Types of SQL Constraints
- NOT NULL
- UNIQUE
- CHECK
- Primary Key
- Foreign Key
Each type of SQL Constraint is described in below with example.
SQL NOT NULL Constraint.
By default, a column can hold NULL value. If we
not want to allow NULL or empty value in a column of our table, then we need to place an
SQL Constraint on this column
specifying that NULL empty column is now not an allowable value.
For example, in the following statement,
Example of NOT NULL Constraint
CREATE TABLE Employees
(empID integer NOT NULL,
FirstName varchar (30) NOT NULL,
LastName varchar(30));
Columns "empID" and "FirstName" cannot include NULL, while "LastName" can
include NULL, while inserting the record in this table we must provide empID and
FirstName after this SQL NOT NULL Constraints apply.
SQL UNIQUE Constraint.
The UNIQUE SQL constraint ensures that all values in a column are distinct and
unique values repetition will not allow after applying SQL UNIQUE Constraint.
For example, the following statement explains real example of SQL UNIQUE
Constraints.
Example of SQL UNIQUE Constraint
CREATE TABLE Employees
(empID integer UNIQUE,
FirstName varchar (30),
LastName varchar(30));
Column "empID" cannot include duplicate values,
while such constraint does not hold for columns
"FirstName" and "LastName" so these last two can hold duplication.
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.
SQL CHECK Constraint.
The SQL CHECK constraint ensures that all values in a column satisfy certain
conditions, suppose we are wishing to enter some conditional record like age of
employ not less then 20 so we need to verify it with SQL CHECK Constraint.
For example, in the following statement explains how to use the SQL CHECK
Constraint in our queries.
Example of SQL CHECK Constraint
CREATE TABLE Employees
(empID integer CHECK (empID > 10),
FirstName varchar (30),
LastName varchar(30));
Column "empID" must only include integers greater than 10. Less then 10
if we try to enter a record then the constraint will force us to enter grater
then 10, so we can't enter less then 10 in the "empID" column.
Please note that the SQL CHECK constraint does not get enforced by MySQL
database at this time.
Primary Key and Foreign Key are discussed in the next two coming SQL
Tutorial sections of PRIMARY Key Constraint
and FOREIGN Key Constrint..
Back
Next
| 
Home 