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 :

Constraint Description

NOT NULL This constraint confirms that a column cannot store NULL value.

UNIQUE This constraint ensures that each row for a column must have a different value.

PRIMARY This constraint is a combination of a NOT NULL constraint and a UNIQUE constraint.
KEY This constraint ensures that the specific column or combination of two or more columns for
a table have a unique identity which helps to find a particular record in a table more easily
and quickly.

CHECK A check constraint ensures that the value stored in a column meets a specific condition.

DEFAULT This constraint provides a default value when specified none for this column.

FOREIGN A foreign key constraint is used to ensure the referential integrity of the data. in one table to
KEY match values in another table.

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 :

Field Name Data Type Size Decimal Places NULL

agent_code char 6 No

agent_name char 25 No

working_area char 25 No

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'

CREATE TABLE mytest (

-- Column: agent_code with a data type CHAR(6), NOT NULL

agent_code CHAR(6) NOT NULL,

 -- Column: agent_name with a data type CHAR(25), NOT NULL

agent_name CHAR(25) NOT NULL,

-- Column: working_area with a data type CHAR(25), NOT NULL

working_area CHAR(25) NOT NULL

);

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Copy
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:

Field Name Data Type Size Decimal Places NULL Constraint

ord_num decimal 6 No UNIQUE

ord_amount decimal 12 2 Yes

ord_date date No

cust_code char 6 No

agent_code char 6 No

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'

CREATE TABLE mytest (

-- Column: ord_num with a decimal data type (6 digits, 0 decimal places), NOT NULL, and UNIQUE
constraint

ord_num DECIMAL(6) NOT NULL UNIQUE,

-- Column: ord_amount with a decimal data type (12 digits, 2 decimal places)

ord_amount DECIMAL(12, 2),

-- Column: ord_date with a date data type, NOT NULL

ord_date DATE NOT NULL,

-- Column: cust_code with a data type CHAR(6), NOT NULL

cust_code CHAR(6) NOT NULL,

-- Column: agent_code with a data type CHAR(6), NOT NULL

agent_code CHAR(6) NOT NULL

);

Copy
To see the structure of the created table :

SQL Code:
DESCRIBE mytest;

Copy
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 :

Field Name Data Type Size Decimal Places NULL Constraint

agent_code char 6 No UNIQUE

agent_name char 25 No UNIQUE

working_area char 25 No

commission decimal 5 2 Yes

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'

CREATE TABLE mytest (

-- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint

agent_code CHAR(6) NOT NULL UNIQUE,

 -- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint

agent_name CHAR(25) NOT NULL UNIQUE,

-- Column: working_area with a data type CHAR(25), NOT NULL

working_area CHAR(25) NOT NULL,

-- Column: commission with a decimal data type (5 digits, 2 decimal places)

commission DECIMAL(5, 2)

);

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Copy
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 :

Field Name Data Type Size Decimal Places NULL Constraint

cust_code char 6 No UNIQUE

cust_name char 25 No

cust_city char 25 No

Grade integer Yes

agent_code char 6 No UNIQUE

the following SQL statement can be used:

SQL Code:

-- Creating a table named 'mytest'

CREATE TABLE mytest (

-- Column: cust_code with a data type CHAR(6), NOT NULL

cust_code CHAR(6) NOT NULL,

-- Column: cust_name with a data type CHAR(25), NOT NULL

cust_name CHAR(25) NOT NULL,

-- Column: cust_city with a data type CHAR(25), NOT NULL

cust_city CHAR(25) NOT NULL,

-- Column: grade with a data type INTEGER

grade INTEGER,

-- Column: agent_code with a data type CHAR(6), NOT NULL

agent_code CHAR(6) NOT NULL,

-- Creating a unique constraint on the combination of cust_code and agent_code columns

UNIQUE(cust_code, agent_code)
);

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Copy
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 :

Field Name Data Type Size Decimal Places NULL Constraint

agent_code char 6 No UNIQUE

agent_name char 25 No UNIQUE

working_area char 25 No

commission integer CHECK

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'

CREATE TABLE mytest (

-- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint

agent_code CHAR(6) NOT NULL UNIQUE,

-- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint

agent_name CHAR(25) NOT NULL UNIQUE,

-- Column: working_area with a data type CHAR(25), NOT NULL

working_area CHAR(25) NOT NULL,

-- Column: commission with a decimal data type, with a CHECK constraint ensuring commission is
less than 1

commission DECIMAL CHECK (commission < 1)

);

Copy
To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Copy
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 -

Field Name Data Type Size Decimal Places NULL Constraint

agent_code char 6 No UNIQUE

agent_name char 25 No UNIQUE

working_area char 25 Yes DEFAULT

commission decimal 8 2 Yes

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'

CREATE TABLE mytest (

-- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint

agent_code CHAR(6) NOT NULL UNIQUE,

 -- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint

agent_name CHAR(25) NOT NULL UNIQUE,

-- Column: working_area with a data type CHAR(25), a DEFAULT value of 'Mumbai' if not specified

working_area CHAR(25) DEFAULT 'Mumbai',

-- Column: commission with a decimal data type (8 digits, 2 decimal places)

commission DECIMAL(8, 2)

);

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Copy
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 -
Field Name Data Type Size Decimal Places NULL Constraint

agent_code char 6 No UNIQUE

agent_name char 25 No UNIQUE

working_area char 25 Yes DEFAULT

commission decimal 8 2 Yes CHECK

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'

CREATE TABLE mytest (

-- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint

agent_code CHAR(6) NOT NULL UNIQUE,

-- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint

agent_name CHAR(25) NOT NULL UNIQUE,

-- Column: working_area with a data type CHAR(25), a DEFAULT value of 'Mumbai' if not specified

working_area CHAR(25) DEFAULT 'Mumbai',

-- Column: commission with a decimal data type (8 digits, 2 decimal places)

commission decimal(8,2)

-- CHECK constraint ensuring commission is greater than 0.1 and less than 0.3

CHECK (commission > 0.1 AND commission < 0.3)

);

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Copy
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 -

Field Name Data Type Size Decimal Places NULL Constraint

agent_code char 6 No UNIQUE

agent_name char 25 No UNIQUE

working_area char 25 No CHECK

commission integer CHECK

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));

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Copy
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 -

Field Name Data Type Size Decimal Places NULL Constraint

ord_num decimal 6 No UNIQUE

ord_amount decimal 12 2 Yes

ord_date char 10 No CHECK

cust_code char 6 No

agent_code char 6 No

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

);

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Copy
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 -

Field Name Data Type Size Decimal Places NULL Constraint

agent_code char 6 No UNIQUE

agent_name char 25 No UNIQUE

working_area char 25 Yes CHECK

commission decimal 8 2 Yes CHECK

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'));

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Copy
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-

Field Name Data Type Size Decimal Places NULL Constraint

agent_code char 6 No UNIQUE

agent_name char 25 No UNIQUE

working_area char 25 Yes CHECK

commission decimal 8 2 Yes CHECK

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')));

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Copy
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 -

Field Name Data Size Decimal NULL Constraint

Type Places

tranno decimal No

company_id varchar 6 Yes FOREIGN

KEY
itemcode varchar 10 Yes PRIMARY
KEY

coname varchar 35 Yes

itemname varchar 35 Yes

iqty integer Yes

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);

Copy
To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

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

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 :

Field Name Data Type Size Decimal Places NULL Constraint

agent_code char 6 No UNIQUE

agent_name char 25 No UNIQUE

working_area char 25 No

commission integer CHECK

-- Creating a table named 'mytest'
CREATE TABLE mytest (
-- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint
agent_code CHAR(6) NOT NULL UNIQUE,

-- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint
agent_name CHAR(25) NOT NULL UNIQUE,

-- Column: working_area with a data type CHAR(25), NOT NULL

working_area CHAR(25) NOT NULL,

-- Column: commission with a decimal data type, with a CHECK constraint ensuring commission is
less than 1
commission DECIMAL CHECK (commission < 1)
);

create table emp (

id number(4) PRIMARY KEY,

Name varchar(100),

sal decimal(10,2) default 10000,

CONSTRAINT FK_deptid varchar(100) FOREIGN KEY(deptid) REFERENCES dept(deptid)

create table dept(

deptid varchar(100) PRIMARY KEY,

dname varchar(100),

dloc varchar(100)

alter table emp add costraint fk_dept_id FOREIGN KEY(dept_id) REFERENCE DEPT(deptid);

alter table emp add constraint on id not null

1
2

d1

d2

d3

insert into emp values (10,'Nina', 20000, d4)

foreign key referential integrity

KEYS: DBMS Normalize form

Primary, composit key, foriegn key

UNION

MINUS

JOIN

Q>

empno job deptno

101 software 10

102 QA 10
103 engineering 10

104 data engg 20

105 developer 30

106 manager 20

107 software 20

108 QA 20

109 Engineer 20

110 vice preside 40

This is the data.Please write me a query to get the jobs common in dept 10 and 20