Introduction to DBMS
Integrity Constraints
Ms. Jyoti Snehi
Department of Computer Science and Engineering
Chitkara University, Punjab
Faculty Name - Jyoti Snehi
8/3/2021 1
GP-19,21,27
Objectives
After completing this lesson, you should be able to
do the following:
• Describe constraints
• Create and maintain constraints
Faculty Name - Jyoti Snehi
8/3/2021 2
GP-19,21,27
Relational Constraints
Faculty Name - Jyoti Snehi
8/3/2021 3
GP-19,21,27
What Are Constraints?
• Constraints enforce rules at the table level.
• Constraints prevent the deletion of a table if there are
dependencies.
• The following constraint types are valid:
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK
Faculty Name - Jyoti Snehi
8/3/2021 4
GP-19,21,27
Constraint Guidelines
• Name a constraint or the Oracle server generates a name by
using the SYS_Cn format.
• Create a constraint either:
– At the same time as the table is created, or
– After the table has been created
• Define a constraint at the column or table level.
• View a constraint in the data dictionary.
Faculty Name - Jyoti Snehi
8/3/2021 5
GP-19,21,27
Defining Constraints
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));
Faculty Name - Jyoti Snehi
8/3/2021 6
GP-19,21,27
Defining Constraints
• Column constraint level
column [CONSTRAINT constraint_name] constraint_type,
• Table constraint level
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
Faculty Name - Jyoti Snehi
8/3/2021 7
GP-19,21,27
The NOT NULL Constraint
Ensures that null values are not permitted for the
column:
NOT NULL constraint NOT NULL Absence of NOT NULL
(No row can contain constraint constraint
a null value for (Any row can contain
this column.) null for this column.)
Faculty Name - Jyoti Snehi
8/3/2021 8
GP-19,21,27
The NOT NULL Constraint
Is defined at the column level:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL, System
salary NUMBER(8,2), named
commission_pct NUMBER(2,2),
hire_date DATE
CONSTRAINT emp_hire_date_nn User
NOT NULL, named
...
Faculty Name - Jyoti Snehi
8/3/2021 9
GP-19,21,27
The UNIQUE Constraint
UNIQUE constraint
EMPLOYEES
…
INSERT INTO
Allowed
Not allowed:
already exists
The UNIQUE Constraint
Defined at either the table level or the column level:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));
Faculty Name - Jyoti Snehi
8/3/2021 11
GP-19,21,27
The PRIMARY KEY Constraint
DEPARTMENTS
PRIMARY KEY
Not allowed INSERT INTO
(Null value)
Not allowed
(50 already exists)
The PRIMARY KEY Constraint
Defined at either the table level or the column level:
CREATE TABLE departments(
department_id NUMBER(4),
department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
Faculty Name - Jyoti Snehi
8/3/2021 13
GP-19,21,27
The FOREIGN KEY Constraint
DEPARTMENTS
PRIMARY
KEY
…
EMPLOYEES
FOREIGN
KEY
… Not allowed
INSERT INTO (9 does not
exist)
Allowed
The FOREIGN KEY Constraint
Defined at either the table level or the column level:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
Faculty Name - Jyoti Snehi
8/3/2021 15
GP-19,21,27
FOREIGN KEY Constraint
Keywords
• FOREIGN KEY: Defines the column in the child table at the
table constraint level
• REFERENCES: Identifies the table and column in the parent
table
• ON DELETE CASCADE: Deletes the dependent rows in the
child table when a row in the parent table is deleted.
• ON DELETE SET NULL: Converts dependent foreign key
values to null
Faculty Name - Jyoti Snehi
8/3/2021 16
GP-19,21,27
The CHECK Constraint
• Defines a condition that each row must satisfy
• The following expressions are not allowed:
– References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM
pseudocolumns
– Calls to SYSDATE, UID, USER, and USERENV functions
– Queries that refer to other values in other rows
..., salary NUMBER(2)
CONSTRAINT emp_salary_min
CHECK (salary > 0),...
Faculty Name - Jyoti Snehi
8/3/2021 17
GP-19,21,27
Adding a Constraint Syntax
Use the ALTER TABLE statement to:
• Add or drop a constraint, but not modify its structure
• Enable or disable constraints
• Add a NOT NULL constraint by using the MODIFY clause
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
Faculty Name - Jyoti Snehi
8/3/2021 18
GP-19,21,27
Adding a Constraint
Add a FOREIGN KEY constraint to the EMPLOYEES
table indicating that a manager must already exist as
a valid employee in the EMPLOYEES table.
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
Table altered.
Faculty Name - Jyoti Snehi
8/3/2021 19
GP-19,21,27
Dropping a Constraint
• Remove the manager constraint from the EMPLOYEES table.
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
Table altered.
• Remove the PRIMARY KEY constraint on the
DEPARTMENTS table and drop the associated FOREIGN
KEY constraint on the EMPLOYEES.DEPARTMENT_ID
column.
ALTER TABLE departments
DROP PRIMARY KEY CASCADE;
Table altered.
Faculty Name - Jyoti Snehi
8/3/2021 20
GP-19,21,27
Disabling Constraints
• Execute the DISABLE clause of the ALTER TABLE
statement to deactivate an integrity constraint.
• Apply the CASCADE option to disable dependent integrity
constraints.
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
Table altered.
Faculty Name - Jyoti Snehi
8/3/2021 21
GP-19,21,27
Enabling Constraints
• Activate an integrity constraint currently disabled in the
table definition by using the ENABLE clause.
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
Table altered.
• A UNIQUE or PRIMARY KEY index is automatically
created if you enable a UNIQUE key or PRIMARY KEY
constraint.
Faculty Name - Jyoti Snehi
8/3/2021 22
GP-19,21,27
Cascading Constraints
• The CASCADE CONSTRAINTS clause is used along with
the DROP COLUMN clause.
• The CASCADE CONSTRAINTS clause drops all referential
integrity constraints that refer to the primary and unique keys
defined on the dropped columns.
• The CASCADE CONSTRAINTS clause also drops all
multicolumn constraints defined on the dropped columns.
Faculty Name - Jyoti Snehi
8/3/2021 23
GP-19,21,27
Cascading Constraints
Example:
ALTER TABLE test1
DROP (pk) CASCADE CONSTRAINTS;
Table altered.
ALTER TABLE test1
DROP (pk, fk, col1) CASCADE CONSTRAINTS;
Table altered.
Faculty Name - Jyoti Snehi
8/3/2021 24
GP-19,21,27
Viewing Constraints
Query the USER_CONSTRAINTS table to view all
constraint definitions and names.
SELECT constraint_name, constraint_type,
search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
Faculty Name - Jyoti Snehi
8/3/2021 25
GP-19,21,27
Viewing the Columns Associated with
Constraints
View the columns associated with the constraint
names in the USER_CONS_COLUMNS view.
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
Faculty Name - Jyoti Snehi
8/3/2021 26
GP-19,21,27
Summary
In this lesson, you should have learned how to create
constraints.
• Types of constraints:
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK
• You can query the USER_CONSTRAINTS table to view all
constraint definitions and names.
Faculty Name - Jyoti Snehi
8/3/2021 27
GP-19,21,27
