SQL CONSTRAINTS

CONSTRAINTS

The SQL CONSTRAINTS are an integrity which defines some conditions that restrict the column to remain true while inserting or updating or deleting data in the column. Constraints can be specified when the table created first with CREATE TABLE statement or at the time of modification of the structure of an existing table with ALTER TABLE statement.

The SQL CONSTRAINTS are used to implement the rules of the table. If there is any violation of the constraints caused some action not performing properly on the table the action is aborted by the constraint.

Some CONSTRAINTS can be used along with the SQL CREATE TABLE statement.

The general structure of the SQL CONSTRAINT is defined as:

The CONSTRAINT keyword is followed by a constraint name followed by a column or a list of columns.

Types of SQL CONSTRAINTS

The SQL provides following types of CONSTRAINTS :

Syntax:

CREATE TABLE <table_name>(
column1    data_type[(size)]   constraint,
column2    data_type[(size)]   constraint,
...);

Parameters:

SQL CREATE TABLE with constraint to exclude NULL value

The following topic will describe how the NOT NULL CONSTRAINT confirms that a column can not have NULL value in CREATE TABLE statement.

Example:

The following example creates a table. Here is the field name and data types :

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
agent_code char(6) NOT NULL,
agent_name char(25) NOT NULL,
working_area char(25) NOT NULL) ;

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE to check unique value

In the following topic, we are going to discuss, how the SQL UNIQUE CONSTRAINT ensures that each row for a column has different values in CREATE TABLE statement.

Example:

The following example creates a table. Here is the field name and data types:

the following SQL statement can be used :

SQL Code:

CREATE TABLE  mytest(
ord_num decimal(6) NOT NULL UNIQUE,
ord_amount decimal(12,2),
ord_date date NOT NULL,
cust_code char(6) NOT NULL,
agent_code char(6) NOT NULL);

To see the structure of the created table :

SQL Code:

DESCRIBE mytest; 

Output:

SQL CREATE TABLE to check unique value on more columns

The following example creates a table. Here is the field name and data types :

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE,
agent_name char(25) NOT NULL UNIQUE,
working_area char(25) NOT NULL,
commission decimal(5,2));

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE with UNIQUE CONSTRAINT

The SQL UNIQUE constraint is used to ensure that the each row for a column have a different value. In this page we are going to discuss, how the SQL UNIQUE CONSTRAINT works if it is used at the end of the CREATE TABLE statement instead of using the UNIQUE CONSTRAINT in the specific columns.

Example :

The following example creates a table. Here is the field name and data types :

the following SQL statement can be used:

SQL Code:

CREATE TABLE mytest(
cust_code char(6) NOT NULL,
cust_name char(25) NOT NULL,
cust_city char(25) NOT NULL,
grade integer,
agent_code char(6) NOT NULL,
UNIQUE(cust_code,agent_code));

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE with CHECK CONSTRAINT

The SQL CHECK CONSTRAINT ensures that a value for a specific column or columns has satisfied a specified condition.

The job of CHECK constraint is, to limit the values for a column of a table.

Note :

• The SQL CHECK CONSTRAINT can not be used on a VIEW.
• The SQL CHECK CONSTRAINT can not be used in a subquery.
• The SQL CHECK CONSTRAINT can also be used in ALTER TABLE and DROP TABLE statement.

The following example creates a table. The table contains a CHECK CONSTRAINT on commission column.The constraint ensures that the 'commission' must be less than 1. Here is the field name and data types :

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) NOT NULL ,
commission decimal CHECK( commission<1));

To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Output :

SQL CREATE TABLE using DEFAULT CONSTRAINT

The SQL DEFAULT CONSTRAINT provides a default value when specified none for a column.

Example:

To include a DEFAULT CONSTRAINT on 'working_area' column which ensures that -

1. The 'working_area' should be 'Mumbai' when specified none for this column,

at the time of creating a table whose field names and data types are -

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) DEFAULT 'Mumbai',
commission decimal(8,2)); 

To see the structure of the created table :

SQL Code:

DESCRIBE mytest; 

Output :

SQL CREATE TABLE using default values and CHECK CONSTRAINT

In the following topic, we have discussed the usage of SQL DEFAULT CONSTRAINT and usage of SQL CHECK CONSTRAINT while creating a table.

Example:

To include a CHECK CONSTRAINT on 'commission' and a DEFAULT CONSTRAINT on 'working_area' column which ensures that -

1. The 'commission' must be more than .1 and less than .3,

2. The 'working_area' should be 'Mumbai' when specified none for this column,

at the time of creating a table which contains the following field names and data types -

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) DEFAULT 'Mumbai',
commission decimal(8,2) 
CHECK(commission>.1 AND commission<.3));

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output :

SQL CREATE TABLE with CHECK CONSTRAINT and IN operator

In the following topic, we have discussed how to use SQL IN operator along with SQL CHECK CONSTRAINT.

The condition for CHECK CONSTRAINT can be defined using any of the basic comparison operator, such as (>, <, =,>=,<=,<>) as well as BETWEEN, IN, LIKE, and NULL operator.

Example:

To include two CHECK CONSTRAINT which are -

1. The first one is on 'working_area' column which ensures that the working_area should be either 'London' or 'Brisban' or 'Chennai' or 'Mumbai',

2. The second one is on 'commission' column which ensures that commission must be less than 1,

in the following table which field name and data types are -

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) CHECK
( working_area IN('London','Brisban','Chennai','Mumbai')) ,
commission decimal CHECK(commission<1)); 

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE with CHECK CONSTRAINT and LIKE operator

In the following topic, we are going to discuss, how the SQL LIKE operator can be used with CHECK CONSTRAINT.

Example:

To include a CHECK CONSTRAINT on 'ord_date' column which ensures that the format of the 'ord_date' must be like '--/--/----', for example, ('18/05/1998') at the time of creating a table with following field names and data types -

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
ord_num decimal(6) NOT NULL UNIQUE ,
ord_amount decimal(12,2) ,
ord_date char(10) NOT NULL CHECK
(ord_date LIKE '--/--/----' ),
cust_code char(6) NOT NULL ,
agent_code char(6) NOT NULL
);    

To see the structure of the created table :

SQL Code:

DESCRIBE mytest; 

Output :

SQL CREATE TABLE with CHECK CONSTRAINT and OR operator

In the following topic, we are discussing about the usage of OR operator along with the CHECK CONSTRAINT.

Example:

To include a CHECK CONSTRAINT on 'commission' and 'working_area' column which ensures that the 'commission' must be less than .20 and 'working_area' must be 'London' at the time of creating the following table which consists the field names and data types -

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) ,
commission decimal(8,2) ,
CHECK(commission<.20 OR working_area='London')); 

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output :

SQL CREATE TABLE using CHECK CONSTRAINT and AND, OR operator

In the following topic, we are going to discuss the usage of OR and AND operator along with the CHECK CONSTRAINT. The condition will start to work at the time of inserting the records in the table.

Example:

To include a CHECK CONSTRAINT on 'commission' and 'working_area' column which ensures that -

1. The 'commission' must be less than .14 and 'working_area' must be 'London',

2. or the 'commission' must be less than .15 and 'working_area' must be 'Mumbai',

3. or the 'commission' must be less than .13 and 'working_area' must be 'New York'

at the time of creating the table which fields name and data types are-

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest (
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) ,
commission decimal(8,2) ,
CHECK((commission<.14 AND working_area='London')
OR (commission<.15 AND working_area='Mumbai')
OR (commission<.13 AND working_area='New York'))); 

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output :

SQL CREATE TABLE using CASCADE

The CASCADE option with ON DELETE allows deleting rows from the child table when the corresponding rows are deleted from the parent table.

The DELETE CASCADE works across a foreign key link and removes the child records associated with the parent records.

Example:

To create a table which contains the following field name and data types -

The table contains a PRIMARY KEY on 'itemcode' and a FOREIGN KEY on 'company_id' column which references to the 'company_id' column of 'company' table.

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
tranno decimal NOT NULL,
company_id varchar(6),
itemcode varchar(10),
coname varchar(35),
itemname varchar(35),
iqty integer,
PRIMARY KEY(itemcode),
FOREIGN KEY(company_id)
REFERENCES company (company_id)
ON DELETE CASCADE);

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Foreign Key
Next: Alter Table