Database Integrity
Integrity 1
Integrity Enforcement
Integrity enforcement is usually split between the
DBMS and the application programs.
Using application programs for integrity
assertions has disadvantages.
- Programming is more complex
- Integrity constraints may be repeated
- Change management is difficult
- Constraints may contradict
- Ad hoc operations may avoid the constraints
Integrity 2
Domain Integrity
A domain is a conceptual pool of values from which
one or more attributes draw their actual values.
DOMAIN AGE RANGE 0-127
ATTRIBUTE EMPLOYEE.AGE 16-65
ATTRIBUTE DEPENDENT.AGE 0-60
Two values can only be compared if they come from
the same domain.
Integrity 3
Oracle Support for Integrity: An Example
To define the EMP table owned by SCOTT, you could issue the following statement:
CREATE TABLE scott.emp
(empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL
CONSTRAINT upper_ename CHECK (ename = UPPER(ename)),
job VARCHAR2(9),
mgr NUMBER CONSTRAINT fk_mgr REFERENCES scott.emp(empno),
hiredate DATE DEFAULT SYSDATE,
sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500),
comm NUMBER(9,0) DEFAULT NULL,
deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL
CONSTRAINT fk_deptno REFERENCES scott.dept(deptno) ) ;
This table contains 8 columns. For example, the EMPNO column is of datatype NUMBER
and has an associated integrity constraint named PK_EMP. The HIRDEDATE column is of
datatype DATE and has a default value of SYSDATE.
Integrity 4
Oracle Integrity Constraints: Column
An integrity constraint is a rule that restricts the values for one or more columns in a table.
Column CONSTRAINT clauses can appear in either CREATE TABLE or ALTER TABLE
commands.
[CONSTRAINT constraint]
[[NOT] NULL | UNIQUE | PRIMARY KEY ]
[REFERENCES [user.] table[ (column) ] [ON DELETE CASCADE]
[CHECK (condition) ]
[EXCEPTIONS INTO [user.] table]
[DISABLE ]
Column_constraint syntax that appears in an ALTER TABLE statement can only define or
remove a NOT NULL constraint.
Integrity 5
Oracle Integrity Constraints: Table
A table CONSTRAINT is identical to a column constraint except that it can reference
multiple columns with a single constraint
[CONSTRAINT constraint]
{[NOT] NULL |
[ {UNIQUE | PRIMARY KEY} (column[, column])
[FOREIGN KEY (column[, column])
[REFERENCES [user.] table[ (column[, column]) ] [ON DELETE CASCADE]
[CHECK (condition) ]
[EXCEPTIONS INTO [user.] table]
[DISABLE ]
Integrity 6
Example of an Alter Table
The following statement alters the EMP table and defines and enables a NOT NULL
constraint on the SAL column:
ALTER TABLE emp
MODIFY (sal NUMBER CONSTRAINT nn_sal NOT NULL)
NN_SAL ensures that no employee in the table has a null salary.
Integrity 7
Examples of Unique in Oracle
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(9) CONSTRAINT unq_dname UNIQUE,
loc VARCHAR2(10) ) ;
The constraint UNQ_DNAME identifies the DNAME column as a unique key. This
constraint ensures that no two departments in the table have the same name. However, the
constraint does allow departments without names.
Alternatively, you can define and enable this constraint with the table_constraint syntax:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(9),
loc VARCHAR2(10),
CONSTRAINT unq_dname UNIQUE (dname)) ;
Integrity 8
UNIQUE Constraints in Oracle
The UNIQUE constraint designates a column or combination of columns as a unique key.
To satisfy a UNIQUE constraint, no two rows in the table can have the
same value for the unique key. However, the unique key made up of a single column can
contain nulls.
A unique key column cannot be of datatype LONG or LONG RAW. You cannot designate
the same column or combination of columns as both a unique key and a primary key or as
both a unique key and a cluster key. However, you can designate the same column or
combination of columns as both a unique key and a foreign key.
You can define a unique key on a single column with column_constraint syntax.
Integrity 9
Defining Composite Unique Keys
To define a composite unique key, you must use table_constraint syntax, rather than
column_constraint syntax.
To satisfy a constraint that designates a composite unique key, no two rows in the table can
have the same combination of values in the key columns. Also, any row that contains nulls in
all key columns automatically satisfies the constraint. However, two rows that contain nulls
for one or more key columns and the same combination of values for the other key columns
violate the constraint.
The following statement defines and enables a composite unique key on the combination of
the CITY and STATE columns of the CENSUS table:
ALTER TABLE census
ADD CONSTRAINT unq_city_state
UNIQUE (city, state)
EXCEPTIONS INTO bad_keys_in_ship_cont
Integrity 10
PRIMARY KEY Constraints in Oracle
A PRIMARY KEY constraint designates a column or combination of columns as the table's
primary key. To satisfy a PRIMARY KEY constraint, both of the following conditions must
be true:
No primary key value can appear in more than one row in the table.
No column that is part of the primary key can contain a null.
A table can have only one primary key.
A primary key column cannot be of datatype LONG or LONG RAW. You cannot designate
the same column or combination of columns as both a primary key and a unique key or as
both a primary key and a cluster key. However, you can designate the same column or
combination of columns as both a primary key and a foreign key.
Integrity 11
Defining Primary Keys in Oracle
You can use the column_constraint syntax to define a primary key on a single column.
The following statement creates the DEPT table and defines and enables a primary key on
the DEPTNO column:
CREATE TABLE dept
(deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
dname VARCHAR2(9),
loc VARCHAR2(10) )
The PK_DEPT constraint identifies the DEPTNO column as the primary key of the
DEPTNO table. This constraint ensures that no two departments in the table
have the same department number and that no department number is NULL.
Integrity 12
Defining Primary Keys in Oracle
Alternatively, you can define and enable this constraint with table_constraint syntax:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(9),
loc VARCHAR2(10),
CONSTRAINT pk_dept PRIMARY KEY (deptno) )
Defining Composite Primary Keys
A composite primary key is a primary key made up of a combination of columns. Because
Oracle creates an index on the columns of a primary key, a composite primary key can
contain a maximum of 16 columns. To define a composite primary key, you must use the
table_constraint syntax, rather than the column_constraint syntax.
Integrity 13
Use of the Disable Option in Oracle
The following statement defines a composite primary key on the combination of the
SHIP_NO and CONTAINER_NO columns of the SHIP_CONT table:
ALTER TABLE ship_cont
ADD PRIMARY KEY (ship_no, container_no) DISABLE
This constraint identifies the combination of the SHIP_NO and CONTAINER_NO columns
as the primary key of the SHIP_CONTAINER. The constraint
ensures that no two rows in the table have the same values for both the SHIP_NO column
and the CONTAINER_NO column.
The CONSTRAINT clause also specifies the following properties of the constraint:
Since the constraint definition does not include a constraint name, Oracle generates a name
for the constraint. The DISABLE option causes Oracle to define the constraint but not
enforce it. At a later time the ENABLE clause can be used to enable a single integrity
constraint or all triggers associated with the table.
Integrity 14
Referential Integrity
DEPT
EMP
DEPT Eno Ename Dno
Dno Dname E1 Smith D5
D5 Research E2 Black D6
EMP
D6 Advertising E3 Jones D6
D7 Newprojects
E4 Brown is to be inserted. What check must be made to maintain integrity?
An attempt to delete D5 Research occurs. What possible actions might take
place to maintain integrity?
Integrity 15
To Maintain Referential Integrity
Event Action
Delete of Parent Delete corresponding Child records
(Cascading Delete) OR
Update of Primary Update Foreign Key of corresponding
Key of Parent Child records (Cascading Update)
Set the Foreign Key to null in the corresponding
Parent Child records
(Delete or Update Nullifies)
Do not allow the delete or update of the Parent
record if any corresponding Child records
Child exist(ie. the event fails to proceed)
(Restricted Delete or Update)
Check that null or a valid Primary Key from the
Insert of Child
Parent has been specified for the Foreign Key
Integrity 16
Oracle Referential Integrity Constraints
The following statement creates the EMP table and defines and enables a foreign key on the
DEPTNO column that references the primary key on the DEPTNO column of the DEPT
table:
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno) )
Integrity 17
Keywords in Oracle Referential Integrities
ON DELETE CASCADE
allows deletion of referenced key values in the parent table that have dependent rows in the
child table and causes Oracle to automatically delete dependent rows from the child table
to maintain referential integrity.
If you omit this option, Oracle forbids deletions of referenced key values in the parent table
that have dependent rows in the child table.
You can define multiple foreign keys in a table. Also, a single column can be part of more
than one foreign key.
Integrity 18
Example With Table_constraint Syntax
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
CONSTRAINT fk_deptno
FOREIGN KEY (deptno)
REFERENCES dept(deptno) )
Note that the foreign key definitions in both of the above statements omit the ON DELETE
CASCADE option, causing Oracle to forbid the deletion of a department if any employee
works in that department. If you use the ON DELETE CASCADE option, Oracle permits
deletions of referenced key values in the parent table and automatically deletes dependent
rows in the child table to maintain referential integrity.
Integrity 19
The ON DELETE CASCADE Option
This example creates the EMP table, defines and enables the referential integrity constraint
FK_DEPTNO, and uses the ON DELETE CASCADE option:
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT fk_deptno
REFERENCES dept(deptno)
ON DELETE CASCADE )
Integrity 20
A Foreign Key Constraint on two Columns
ALTER TABLE phone_calls
ADD CONSTRAINT fk_areaco_phoneno
FOREIGN KEY (areaco, phoneno)
REFERENCES customers(areaco, phoneno)
EXCEPTIONS INTO wrong_numbers
The constraint FK_AREACO_PHONENO ensures that all the calls in the PHONE_CALLS
table are made from phone numbers that are listed in the CUSTOMERS table. Before you
define and enable this constraint, you must define and enable a constraint that designates the
combination of the AREACO and PHONENO columns of the CUSTOMERS table as a
primary or unique key.
The EXCEPTIONS option causes Oracle to write information into WRONG_NUMBERS
table about any rows in the PHONE_CALLS table that violate the constraint.
Integrity 21
Example of a CHECK Constraint on a Column
CREATE TABLE dept (deptno NUMBER CONSTRAINT check_deptno
CHECK (deptno BETWEEN 10 AND 99)
DISABLE,
dname VARCHAR2(9) CONSTRAINT check_dname
CHECK (dname = UPPER(dname))
DISABLE,
loc VARCHAR2(10) CONSTRAINT check_loc
CHECK (loc IN ('DALLAS','BOSTON',
'NEW YORK','CHICAGO'))
DISABLE)
Unlike other types of constraints, a CHECK constraint defined with column_constraint
syntax can impose rules on any column in the table, rather than only on the column in
which it is defined.
Because each CONSTRAINT clause contains the DISABLE option, Oracle only
defines the constraints and does not enforce them.
Integrity 22
Example of a CHECK Constraint on a Table
The following statement creates the EMP table and uses a table constraint clause to define and
enable a CHECK constraint:
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
CHECK (sal + comm 0),
cost NUMBER
CONSTRAINT check_cost CHECK (cost > 0) )
Integrity 24
Constraint on a Table
Sometimes it is necessary to defer the checking of certain constraints, most
commonly in the "chicken-and-egg" problem. Suppose we want to say:
CREATE TABLE chicken (cID INT PRIMARY KEY,
eID INT REFERENCES egg(eID));
CREATE TABLE egg(eID INT PRIMARY KEY,
cID INT REFERENCES chicken(cID));
But if we simply type the above statements into Oracle, we'll get an error. The
reason is that the CREATE TABLE statement for chicken refers to table egg,
which hasn't been created yet! Creating egg won't help either, because egg refers
to chicken. To work around this problem, we need SQL schema modification
commands. First, create chicken and egg without foreign key declarations:
CREATE TABLE chicken(cID INT PRIMARY KEY, eID INT);
CREATE TABLE egg(eID INT PRIMARY KEY, cID INT);
Integrity 25
Constraint on a Table
Then, we add foreign key constraints:
ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
FOREIGN KEY (eID) REFERENCES egg(eID)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE egg ADD CONSTRAINT eggREFchicken
FOREIGN KEY (cID) REFERENCES chicken(cID)
INITIALLY DEFERRED DEFERRABLE;
INITIALLY DEFERRED DEFERRABLE tells Oracle to do deferred
constraint checking. For example, to insert (1, 2) into chicken and (2, 1)
into egg, we use:
INSERT INTO chicken VALUES(1, 2);
INSERT INTO egg VALUES(2, 1);
COMMIT;
Integrity 26
Constraint on a Table
Because we've declared the foreign key constraints as "deferred", they are
only checked at the commit point. (Without deferred constraint checking,
we cannot insert anything into chicken and egg, because the first INSERT
would always be a constraint violation.)
Finally, to get rid of the tables, we have to drop the constraints first,
because Oracle won't allow us to drop a table that's referenced by another
table.
ALTER TABLE egg DROP CONSTRAINT eggREFchicken;
ALTER TABLE chicken DROP CONSTRAINT chickenREFegg;
DROP TABLE egg;
DROP TABLE chicken;
Integrity 27
Example of a Trigger
This example creates a BEFORE statement trigger named EMP_PERMIT_CHANGES in the
schema SCOTT. This trigger ensures that changes to employee records are only made during
business hours on working days:
CREATE TRIGGER scott.emp_permit_changes
BEFORE
DELETE OR INSERT OR UPDATE
ON scott.emp
DECLARE
dummy INTEGER;
BEGIN
/* If today is a Saturday or Sunday, then return an error.*/
IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR
TO_CHAR(SYSDATE, 'DY') = 'SUN')
THEN raise_application_error( -20501,
'May not change employee table during the weekend');
END IF;
Integrity 28
/* Compare today's date with the dates of all company holidays. If today is a
company holiday, then return an error. */
SELECT COUNT(*)
INTO dummy
FROM company_holidays
WHERE day = TRUNC(SYSDATE);
IF dummy > 0
THEN raise_application_error( -20501,
'May not change employee table during a holiday');
END IF;
/* If the current time is before 8:00AM or after
6:00PM, then return an error.
*/
IF (TO_CHAR(SYSDATE, 'HH24') = 18)
THEN raise_application_error( -20502,
'May only change employee table during working hours');
END IF;
END;
Integrity 29
A Second Example of a Trigger
This example creates a BEFORE row trigger named SALARY_CHECK in the schema
SCOTT. Whenever a new employee is added to the employee table or an existing employee's
salary or job is changed, this trigger guarantees that the employee's salary falls within the
established salary range for the employee's job:
CREATE TRIGGER scott.salary_check
BEFORE
INSERT OR UPDATE OF sal, job ON scott.emp
FOR EACH ROW
WHEN (new.job 'PRESIDENT')
DECLARE
minsal NUMBER;
maxsal NUMBER;
Integrity 30
BEGIN
/* Get the minimum and maximum salaries for the
employee's job from the SAL_GUIDE table. */
SELECT minsal, maxsal
INTO minsal, maxsal
FROM sal_guide
WHERE job = :new.job;
/* If the employee's salary is below the minimum or */
/* above the maximum for the job, then generate an */
/* error. */
IF (:new.sal maxsal)
THEN raise_application_error( -20601,
'Salary ' || :new.sal || ' out of range for job '
|| :new.job || ' for employee ' || :new.ename );
END IF;
END;
Integrity 31
Oracle CREATE TRIGGER Keywords
OR REPLACE
recreates the trigger if it already exists. You can use this option to change the definition of an
existing trigger without first dropping it.
BEFORE / AFTER
indicates that Oracle fires the trigger before or after executing the triggering statement. For row
triggers, this is a separate firing before or after each affected row is changed.
DELETE
indicates that Oracle fires the trigger whenever a DELETE statement removes a row from the
table.
INSERT
indicates that Oracle fires the trigger whenever an INSERT statement adds a row to table.
UPDATE OF
indicates that Oracle fires the trigger whenever an UPDATE statement changes a value in one
of the columns specified in the OF clause. If you omit the OF clause, Oracle fires the trigger
whenever an UPDATE statement changes a value in any column of the table.
Integrity 32
Oracle CREATE TRIGGER Keywords
ON
specifies the schema and name of the table on which the trigger is to be created. If you omit
schema, Oracle assumes the table is in your own schema. You cannot create a trigger on a table
in the schema SYS.
REFERENCING
specifies correlation names. You can use correlation names in the PL/SQL block and WHEN
clause of a row trigger to refer specifically to old and new values of the current row. The
default correlation names are OLD and NEW. If your row trigger is associated with a table
named OLD or NEW, you can use this clause to specify different correlation names.
FOR EACH ROW
designates the trigger to be a row trigger. Oracle fires a row trigger once for each row that is
affected by the trigger statement and meets the optional trigger constraint (defined in WHEN)
WHEN clause.
specifies the trigger restriction. The trigger restriction contains a SQL condition that must be
satisfied for Oracle to fire the trigger. This condition must contain correlation names and cannot
contain a query. You can only specify a trigger restriction for a row trigger. Oracle evaluates
this condition for each row affected by the triggering statement.
Integrity 33