Maintaining Data Integrity

1
Maintaining Data Integrity with Declarative
Constraints
2
Declarative Constraints

• Oracle can preserve data integrity through the
  use of constraints
• Constraints apply to all rows in the table on
  which they are based
• Cover rows present in the table before the
  constraint is specified plus any rows which are
  subsequently created
• When to use declarative constraints
• To implement simple business rules such as
• salaries should not exceed 5000
• When not to use declarative constraints
• More complex business rules need to be handled by
  application logic within transactions or by the
  use of database triggers
• The following constraints can be specified
• PRIMARY KEY FOREIGN KEY
• UNIQUE CHECK
• NOT NULL
• Constraints are not independent objects
• Can be created and manipulated only via the
  CREATE TABLE and ALTER TABLE statements

3
Primary Key Constraints

• Constraints can be used to enforce
• Entity Integrity
• Referential Integrity
• Entity Integrity requires no duplicate rows in a
  table
• A primary key constraint will enforce entity
  integrity
• EVERY table should have a primary key
• The primary key column(s) will be
• UNIQUE and NOT NULL
• Automatically indexed using an appropriate unique
  index (use can be made of an existing index)
• Recommended to use short numeric columns for
  efficiency in terms of space usage and
  performance

CREATE TABLE emp (empno NUMBER(4) CONSTRAINT
emp_pk PRIMARY KEY,
4
Primary Keys Constraints

• Columns which constitute primary keys should
• Have data values which are unique
• Have data values which are never changed
• Primary keys should not contain meaningful data
  
• Not contain any NULL values
• Be defined as a numeric column and be as short as
  possible
• Preferably be single columns
• Not be composite
• Likely to be long
• Require complex join criteria
• Cannot be assigned simple sequence numbers
• A common naming convention is to use the primary
  key column name(s) with a _pk suffix
• Standardised names are easier to find in the data
  dictionary
• Use ALTER TABLE to place a primary key on an
  existing table

ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY
KEY(deptno)
5
Composite Keys and Surrogate Keys

• Consider the following model

Employee
Project
 
Assignment

• This structure implies a key for assignments to
  be emp,proj
• Could be better to use asst as a surrogate key
• Beware that unique surrogates do not guarantee
  uniqueness in the associated data rows

         
6
Unique and NOT NULL Constraints

• Unique Constraints are similar to primary key
  constraints but allow NULL values
• The number of NULLs is unlimited as all are
  considered unique!
• Any number of unique constraints can be created
  on a table
• A common naming convention is to use the unique
  key column name(s) with a _uk suffix
• NOT NULL constraints do not allow NULLs in a
  column
• Usually defined when a table is created and not
  named

ALTER TABLE emp ADD CONSTRAINT ename_deptno_uk
UNIQUE (ename,deptno)
CREATE TABLE emp ( empno NUMBER(4) NOT NULL,
ename VARCHAR2(20), sal NUMBER(7,2) NOT
NULL, comm NUMBER(7,2), deptno NUMBER(2)
NOT NULL)
7
CHECK Constraints

• Simple business rules can be enforced
• Can make comparisons based on constants and
  column values of only the row being updated
• References to data in other tables via subqueries
  is not possible
• Reference to sysdate and user is not allowed
  because this causes an implicit subquery
• A common naming convention is to use a _ck
  suffix
• Efficient because they are optimised beneath the
  SQL layer
• Not violated if the expression under test
  evaluates to unknown
• A NULL sal will allow a negative comm to pass the
  check
• In this case, it may be advisable to declare both
  columns as NOT NULL

ALTER TABLE emp ADD CONSTRAINT sal_ck CHECK
(sal gt0 OR comm gt0)
8
CHECK Constraint Examples

• Ensure gender values are always uppercase m or
  f
• Ensure that commission is never more than ¼ of an
  employees salary
• Ensure that hiredate is not later than the
  current date
• Error because sysdate can not be used in check
  constraints

CREATE TABLE emp ( gender VARCHAR2(1) CONSTRAINT
gender_ck CHECK (gender IN UPPERCASE(m,f))
ALTER TABLE emp ( ADD CONSTRAINT comm_ck CHECK
(comm lt sal0.25)
ALTER TABLE emp ( ADD CONSTRAINT
hiredate_ck CHECK (hiredate lt sysdate)
9
Referential Integrity

• Maintains integrity of master-detail
  relationships
• Operations on the primary key of the parent table
  are constrained if dependent rows exist in the
  child table
• Operations on the foreign key in the child table
  are prevented if they result in values which do
  not exist in the parent (referenced) table. In
  Oracle, foreign keys may be set to NULL
  regardless of any referential constraint

empno ename mgr deptno ----- --------
---- ------ 7369 SMITH 7566 20 7499
ALLEN 7698 10 7521 WARD 7698
30 7566 JONES 7521 10 7654 MARTIN
7521 10 7698 BLAKE 30  
deptno dname ------ ---------- 10
ACCOUNTING 20 RESEARCH 30 SALES
40 OPERATIONS
Foreign keys
Emp
Dept
10
Foreign Key (Referential) Constraints

• Points at issue
• May be single or composite columns
• Must match data type of the referenced column(s)
• No limit to number of foreign keys
• Cannot be based on views
• Referenced columns must already be PRIMARY KEY or
  UNIQUE columns
• Referenced columns may be in the same table
• A self-referencing foreign key
• Foreign key columns may be NULL or partly NULL
  (regardless of any Foreign Key constraint
• Referential constraints do not automatically
  build indexes on the foreign key columns

11
In-line and Out-of-line Constraints

• An in-line constraint is specified on the same
  line as the column definition
• Also known as a column-level constraint
• An out-of-line constraint is specified in a
  separate clause from its participating column(s
• Also known as a table-level constraint
• Suppose dept has a composite key of divno and
  deptno

CREATE TABLE ... deptno NUMBER (4)
CONSTRAINT emp_dept_fk REFERENCES dept(deptno)
CREATE TABLE ... divno NUMBER(3), deptno
NUMBER (4), CONSTRAINT emp_dept_fk FOREIGN
KEY (divno,deptno) REFERENCES dept(divno,deptno)
12
General Foreign Key Constraint Actions

• Four options are generally recognised for actions
  performed by foreign key constraints
• RESTRICT Prevents any updates to parent keys
  which impact the constraint
• SET NULL Allows parent keys to be changed,
  matching child records will have foreign keys
  set to NULL
• SET DEFAULT Allows parent keys to be changed,
  matching
• child records have foreign keys set to some
  default value
• CASCADE Allows parent keys to be changed,
  matching child records will have foreign keys
  changed to the same value as the parent key
  default value.
• If a parent record is deleted, all matching
  child records will also be deleted

13
Foreign Key Constraint Actions in Oracle

• Oracle uses declarative constraints to support
• Restriction of UPDATEs to referenced columns
•  
• Restriction of DELETEs (DELETE RESTRICT) to
  referenced columns
•  
• DELETE CASCADE (automatic deletion of dependent
  rows)
• No support for UPDATE CASCADE (must be performed
  via triggers or application logic)
• The SQL standard proposes a PENDANT facility
• When the last remaining employee in a department
  is deleted, the department record must also be
  deleted

14
The DELETE CASCADE Action

• Syntax for DELETE CASCADE
• Oracle will report only on rows which are deleted
  from dept when this referential action occurs

ALTER TABLE emp ADD CONSTRAINT emp_dept_fk
FOREIGN KEY (deptno) REFERENCES dept(deptno) ON
DELETE CASCADE
DELETE FROM dept WHERE deptno IN (10,20) 2
rows deleted
15
Runtime Error Messages

• The following error message is received if a
  value due to an insert or update on the foreign
  key does not have a matching value in the primary
  key
• The following error message is received if a
  value which is about to be lost from the parent
  key due to an update or delete, has corresponding
  values in the foreign key

ORA-02291 integrity constraint ltusername.constrai
nt_namegt violated - parent key found
ORA-02292 integrity constraint ltusername.constrai
nt_namegt violated - child record found
16
Conterminous Paths
 
 
TableA
Delete Cascade
Delete Cascade
 
 
TableB
TableC
 
Delete Restrict
 
Delete Cascade
TableD
 

• Each table has one row with the value x
• What would be the effect of

DELETE FROM a WHERE col x
 
17
Self-Referential Constraints

• How can we insert the first row into a table with
  a self-referential foreign key constraint?
• i Insert the row with NULLs in the foreign key
  column(s)
• Must allow NULLs in the foreign key
• ii Add or enable the self-referential constraint
  later
• iii Create first few rows with a single insert
• INSERT INTO tab1 SELECT FROM tab2 ...
• iv Have the first row reference itself
• For example employee has same mgr value as
  his/her own empno
• v Use deferred constraints (see later)

18
Foreign Keys and Nulls

• Single column foreign key values must reflect
  primary key values in the parent table or be set
  to NULL
• Composite foreign key values must reflect primary
  key values in the parent table or be wholly or
  partly set to NULL
• Key values which are partly NULL are not checked
  for the integrity of the NOT NULL part
• Scott passes the integrity check, but Scotts
  NULL value can not effectively be updated
• The row for Ford will be checked out

             
Newdept   Divno Deptno Desc ----- ------
-------- 1 1 Finance 1 2
Sales 2 1 Operations 2 2
Design 2 3 Chemicals
Newemp   Divno Deptno Ename ----- ------
-------- 2 1 Smith 1 2
Adams 2 1 Carter 2 NULL
Best NULL NULL Cox NULL 9 Scott 1
NULL King 2 5 Ford
FK divno,deptno
PK divno,deptno
19
Matching Nulls

• A composite foreign key may be (i) all NULL
• (ii) all non-NULL
• (iii) partially NULL
• There are three possible matching rules for such
  keys
• 1. Match Full All columns must be NULL or
• all columns must have matching values in the
  primary key
• 2. Match Partial All columns must be NULL or
• Some of the columns may be NULL and the
  remainder must match values in their
  respective primary key columns
• 3. Match none All columns must be NULL or
• one or more columns are NULL and the
  remainder may take any value
• Oracle by default uses the Match None rule
• May be converted to match full with a CHECK
  constraint
• Database triggers are needed to establish a Match
  Partial rule

20
Matching Partial NULLs

• Partial NULLs are allowed in foreign keys (ANSI
  standard)
• To prevent partial NULLs use a CHECK constraint
• This will force the Match Full rule for NULLs
• The Match Partial rule can not be properly
  implemented using declarative integrity
  constraints database triggers must be used

CONSTRAINT divno_deptno_ck CHECK ( ((divno IS
NOT NULL) AND (deptno IS NOT NULL)) OR (((divno
IS NULL) AND (deptno IS NULL)))
21
Partial NULL Issues

• Consider the following two tables in which a
  foreign key constraint is placed on the
  concatenation of divno and deptno in the newdept
  table
• What happens if
• (i) The Art department is deleted
• (ii) Both the departments are deleted
• (iii) The Art department is updated to divno 3,
  deptno 4 (assume UPDATE CASCADE is
  implemented)
• (iv) The deptno value for Fox is changed

Newdept   Divno Deptno Desc ----- ------
-------- 1 1 Art 2 2 Sales
Newemp   Divno Deptno Empno Ename ----- ------
----- ----- 1 7777 Cox 1
8888 Roe 6 9999 Fox
PK divno,deptno
FK divno,deptno
22
Manipulating Constraints

• It is important to give constraints a name on
  creation
• Otherwise, Oracle will supply a system generated
  name which will
• serve as the only way to identify the constraint
  
• The constraint name is used in Oracle error
  messages
• System generated constraint names take the form
  of SYS_C00nnn
• So, if the constraint is not explicitly named,
  this system name
• must be known in order to drop/disable/enable
  the constraint
• To change the name of a constraint, its name must
  be known
• To change the behaviour of a constraint

ALTER TABLE emp RENAME CONSTRAINT ltold_namegt TO
ltnew_namegt
ALTER TABLE emp DROP CONSTRAINT
ltconstraint_namegt ALTER TABLE emp ADD
CONSTRAINT ltconstraint_namegt ltconstraint_definitio
ngt
23
Dropping Constraints

• Use the following statement
• The CASCADE option must be used to drop primary
  key constraints if they are being referenced by
  other constraints
• Columns can simply be modified in order to drop
  NOT NULL constraints

ALTER TABLE table_name DROP CONSTRAINT
constraint_name PRIMARY KEY
ALTER TABLE table_nameDROP PRIMARY KEY CASCADE
ALTER TABLE dept DROP PRIMARY KEY CASCADE
ALTER TABLE dept MODIFY dname NULL
24
Enabling/Disabling Constraints
ALTER TABLE emp ENABLE CONSTRAINT emp_pk

• Enforces the constraint
• Checks rows for violations
• Gives details of errors
• Any violations prevent the constraint being
  enabled
• Locks out all activity on the table (even
  readers)
• Builds any associated index (may take some time)
• Relaxes the constraint
• Often done to increase speed of DML (e.g. bulk
  data loads)
• Drops any associated index
• Constraints are enabled by default on creation

ALTER TABLE emp DISABLE CONSTRAINT emp_pk
25
Constraints and Entity Models

• No constraints (other than referential) on the
  foreign key
• Additional NOT NULL constraint on the foreign key
• Additional UNIQUE constraint on the foreign key
• Additional UNIQUE and NOT NULL constraints on the
  foreign key

X
Y

X
Y

X
Y

X
Y

26
Handling Exceptions

• To deal with rows which are violating (and
  preventing) a constraint
• i Construct an EXCEPTIONS table using the
  UTLEXCPT script
• ii Issue a statement to create a constraint
  for example
• ii This will place the ROWIDs of any offending
  rows in the exceptions table so that the rows
  can be identified and dealt with
• iii Optionally remove all rows causing
  violations (could be dangerous)

ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY
(empno) EXCEPTIONS INTO exceptions
ROW_ID OWNER TABLE_NAME
CONSTRAINT ------------------ ----------
----------- ---------- AAABFJAACAAAFA3AAN SCOTT
EMP EMP_PK AAABFJAACAAAFA4AAN SCOTT
EMP EMP_PK
DELETE FROM emp WHERE ROWID IN (SELECT row_id
FROM exceptions WHERE constraint
ltconstraint_namegt)
27
Deferred Constraints

• Constraint checking can be deferred until commit
  time
• Check is not made at end of DML statement but
  delayed until end of transaction
• If the constraint is violated, the entire
  transaction is rolled back
• INITIALLY DEFERRED
• On creation, constraint is not checked until
  commit
• INITIALLY IMMEDIATE
• On creation, constraint is checked after each DML
  statement (default)
• DEFERRABLE, NOT DEFERRABLE
• Governs whether behaviour of constraint can be
  subsequently changed to DEFERRED or IMMEDIATE

ALTER TABLE table_name ADD CONSTRAINT ...
INITIALLY DEFERRED INITIALLY
IMMEDIATE DEFERRABLE NOT DEFERRABLE
28
Deferred Constraint Example

• Scenario
• The dept table has a primary key on deptno
• The emp table has a deferrable foreign key on
  deptno referencing dept, initially set to
  IMMEDIATE

ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY
KEY(deptno)
ALTER TABLE emp ADD CONSTRAINT emp_dept_fk FOREIGN
KEY (deptno) REFERENCES dept(deptno) INITIALLY
IMMEDIATE DEFERRABLE
29
Deferred Constraint Example (continued)

• It is required to update a department number from
  10 to 99
• Changes will need to be made by separate update
  statements as follows
• 1. UPDATE dept SET deptno 99 WHERE deptno
  10
• 2. UPDATE emp SET deptno 99 WHERE deptno 10
• The first update fails with the following error
  message
• The constraint is checked immediately and
  therefore too early
• We need to change the constraint mode

ORA-02292 integrity constraint
(SCOTT.EMP_DEPT_FK) violated - child record found
30
Changing the Constraint Mode

• Two methods available to toggle constraint modes
• SET CONSTRAINT
• Used to change the mode for a single transaction
• ALTER SESSION
• Changes mode for all deferrable constraints for
  an entire session
• Reset to default (initial) validation using the
  keyword DEFAULT

SET CONSTRAINT constraint_name,...,constraint_name
IMMEDIATE DEFERRED
SET CONSTRAINTS ALL IMMEDIATE DEFERRED
ALTER SESSION SET CONSTRAINTS IMMEDIATE
DEFERRED
ALTER SESSION SET CONSTRAINTS DEFAULT
31
Processing the Update Transaction

• Set the constraint to be deferred until the end
  of the transaction
• When the commit occurs
• Execute both updates
• These both succeed in changing deptno values in
  emp and dept
• from 10 to 99
• Issue the commit
• The entire transaction will also succeed as all
  the data is now consistent and complies with the
  foreign key constraint as it finally gets checked
  on commit

SET CONSTRAINT emp_dept_pk DEFERRED
32
Enforced Constraints

• Existing data is not checked
• Checks made only on subsequent changes after
  enabling the constraint
• Used when constraints do not apply to historical
  data or when it is known that existing data
  already complies with the constraint
• Example No new employees can have a salary gt
  3000
• Constraint is created even though existing rows
  violate it
• Attempt to insert a new row which violates the
  constraint
• Rejected with the following error message

ALTER TABLE emp ADD CONSTRAINT sal_ck CHECK (sal
lt3000) ENABLE NOVALIDATE
INSERT INTO emp (empno,ename,sal,deptno) VALUES
(8888,COX,5500,10)
ORA-02290 check constraint (SCOTT.EMP_CK)
violated
33
Validating Enforced Constraints

• After eliminating all violations, the constraint
  can be validated so that it acts upon all rows
  in the table
• If Kings salary of 5000 is still present the
  following error message is generated

ALTER TABLE emp ENABLE VALIDATE CONSTRAINT sal_ck
ORA-02293 cannot enable (SCOTT.EMP_CK) - check
constraint violated
34
Constraints in the Data Dictionary

• Details of constraints can be found in
  user_constraints
• The constraint_type column can have the following
  values
• C check constraint
• P primary key constraint

SELECT table_name ,constraint_name
,constraint_type ,r_constraint_name
,status FROM user_constraints
TABLE_NAME CONSTRAINT_NAME C
R_CONSTRAINT_NAME STATUS --------------
---------------- - ----------------- ------- DEPT
DEPT_PK P
ENABLED EMP EMP_PK P
ENABLED EMP EMP_JOB_CK
C DISABLED EMP
SYS_C001415 C ENABLED EMP
EMP_DEPT_FK R DEPT_PK
ENABLED
35
Constraints in the Data Dictionary (continued)

• Columns suffering constraints are found in
  user_cons_columns

SELECT constraint_name ,table_name
,column_name ,position FROM
user_cons_columns
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
POSITION ----------------- ------------
------------ -------- EMP_JOBDEPTNO_UK EMP
JOB 1 EMP_JOBDEPTNO_UK EMP
DEPTNO 2 DEPT_PK
DEPT DEPTNO 1 EMP_PK
EMP EMPNO 1
36
Summary

• Declarative constraints are processed beneath the
  SQL layer and
• hence are efficient viz
• To check if an employees department exists,
  requires a SELECT
• on the department table, if the check is
  performed with application
• code or database triggers
• Constraints
• Are never circumvented
• Apply to all rows
• Apply to all applications
• Simple to specify
• Once only definition
• Efficient particularly for client/server
  applications
• Cannot be used for complex business rules
• Are not checked until data is written to the
  database time
• Do not implement all referential actions
• Are often incompatible with database triggers