Data integrity

1. Data Integrity
Chapter 11

2. Definition
Correctness and
completeness of data
in a database

3. When is data integrity lost?
Invalid data added to the database
Existing data modified to incorrect value
Changes to database lost
Changes partially applied

4. How is data integrity preserved?
Through Data Integrity Constraints
Constraints restrict data values that
can be inserted or updated

5. Types of Data Integrity Constraints
Required Data:Columns must contain valid
data value in every row,NOT NULL
Validity Checking:Columns must contain data
within the domain,set of values legal for a
column-data type,CHECK,Domain
Entity Integrity:Primary key must contain
unique value in each row

6. Types of Data Integrity
Constraints(contd.)
Referential Integrity:Foreign Key linking each
row of child table to matching primary key
value in parent table
Other data relationships:additional constraints
governing legal data values
Business Rules
Consistency:Multiple updates involving many
tables may make database inconsistent

7. Required data
NOT NULL constraint in CREATE TABLE statement
Ensures that each INSERT statement specifies a non
null value (Error for null)
Ensures that each UPDATE statement specifies a non
null value (Error for null)
Must be specified at table creation time
Adding NOT NULL constraint after data values are
added may result in error if any null values found for
the column

8. Validity Checking
Data type ensures specific values of a type to
be present for the column
Two features to support validity checking-
column check constraints and domains

9. Validity Checking (contd.)
Column CHECK constraints – Search condition
that produces true/false values
Specified with CREATE TABLE
With the CHECK constraint ,DBMS checks
value of that column each time a new row is
inserted or updated
Allows if condition true
Error if condition false

10. Validity Checking(contd.)
CHECK CONSTRAINT EXAMPLE
Create table test
(rollno number(2) check (rollno between 1 and 80),
name varchar2(15) );
Insert into test values(55,’ANANYA’);
1 row inserted
Insert into test values(85,’AKSHAT’);
ERROR-Check constraint violated

11. Validity Checking(contd.)
Domains (SQL2)
Generalizes CHECK Constraint
Same CHECK constraint can be applied to
many different columns within a database
through DOMAIN-a collection of legal data
values
Domain defined with a name and a search
condition for range of legal data values
CREATE DOMAIN statement

12. Validity Checking(contd.)
Domain example
Create DOMAIN dom_val_bet number(2)
CHECK (value between 1 and 60);
Create TABLE test1
(rollno dom_val_bet, name varchar2(15) );
Create table test2
(rollno dom_val_bet, marks number(3) );

13. Validity Checking(contd.)
Why use DOMAIN???
Can be used repeatedly simplifying table
definitions
Changing definition later, if required becomes
easy
Specially helpful for large databases

14. Entity Integrity
Each entity is unique
Table’s PRIMARY KEY must have unique values for
each row
DBMS automatically checks uniqueness of primary key
value for each insert or update
Insert or update to include duplicate value in primary
key column results in error
If required for non-primary key column,UNIQUE
constraint or CREATE INDEX
Uniqueness also enforces NOT NULL for primary key

15. Referential Integrity
Foreign key-primary key relationship
Each foreign key value in child table must have
a corresponding value in primary key column of
parent table
Ensures integrity of parent-child relationship in
tables

16. Referential Integrity Problems
Inserting a new child row
Updating the foreign key in a child row
Deleting a parent row
Updating the primary key in a parent row

17. Delete and Update Rules
Delete Rules
Restrict delete rule(default) -prevents deletion of a row
with children
Cascade delete rule-when parent row deleted all child
rows automatically deleted
Set null delete rule – when parent row deleted foreign
key values of all child rows set to null
Set default delete rule -when parent row deleted
foreign key values of all child rows set to default value
set for that column
What to do when User tries to delete a row of
parent table?

18. Delete and Update Rules
Update Rules
Restrict update rule(default) -prevents updation of a
row with children
Cascade update rule-when parent row updated all child
rows automatically updated accordingly
Set null update rule – when parent row updated foreign
key values of all child rows set to null
Set default delete rule -when parent row updated
foreign key values of all child rows set to default value
set for that column
What to do when User tries to update a row
of parent table?

19. Cascaded deletes and Updates
Restrict is a single level rule
Set Null and Set Default are two level rules
Cascade can be a multi level rule
Cascade delete rules must be used with
special care as it may cause widespread
deletion if used incorrectly
Cascade update affects more only if foreign
key of child table is also its primary key

20. Referential Cycles
When a child table’s primary key is referred by
a column of the parent’s table a referential
cycle is formed.
DeptnoDnamelocmgr
Dept
EmpnoEnamedobsaldeptnocomm
Emp

21. Referential cycles (contd.)
Inserting a row in EMP table not allowed as there must
be corresponding deptno value in DEPT table .
Inserting a row in DEPT table not allowed as there
must be corresponding value for mgr in EMP(empno)
Only possible when mgr is allowed null value
Sometimes it is convenient if constraints are not
checked immediately-deferred checking
In referential cycles cascade delete rule should be
given very cautiously

22. Foreign Keys and Null Values
Allowed to have Null values
For handling Null values in foreign key columns options
provided in CREATE TABLE statement:
Match Full:foreign keys in child table fully match
primary key in parent table
Match Partial:Allows null values in parts of the foreign
key,non-null values match the corresponding parts of
primary key in parent table

23. Advanced Constraint Capabilities
FOUR types of Constraints
1.Column Constraints: appear in individual
column definitions in CREATE TABLE
statement
eg.
CREATE TABLE TEST2
( ROLLNO NUMBER PRIMARY KEY,
NAME VARCHAR2(15) );

24. Advanced Constraint Capabilities
(contd.)
1.Domains:
specialized type of column constraint
Provide capability to define new data types
Predefined data type + additional constraints
once created,can be used in place of data type
Defined outside the table with CREATE
DOMAIN statement

25. Advanced Constraint Capabilities
(contd.)
1.Table Constraints:usually appear as a group after
column definitions
Eg. Create table offices
(office number(5) not null,
city varchar2(15),
mgr number(4),
primary key(office),
foreign key(mgr) references salesreps(empno)
on delete set null,
unique(city));

26. Advanced Constraint Capabilities
(contd.)
1.Assertions
Specified outside table definition
Specifies relationship between data values crossing
multiple tables
Create large database processing overhead so
defined with care
Eg. Create assertion cr_ord
check(customer.credit_limit<=
select sum(orders.amount)
from orders
where orders.cust=customer.cust_num);

27. SQL2 Constraint Types
1.NOT NULL
Prevents null values
Column constraint

28. SQL2 Constraint Types (contd.)
PRIMARY KEY unique and not null
Can be a column or a table constraint
When a single column,column constraint is
convenient
When on multiple columns, table constraint
must

29. SQL2 Constraint Types(contd.)
1.UNIQUE
Column or table constraint
When for a single column,column constraint is
convenient
When on combination of multiple columns,
table constraint must

30. SQL2 Constraint Types (contd.)
4.Referential Integrity (Foreign Key) Constraint
Column or table constraint
When for a single column,column constraint is
convenient
When on combination of multiple columns, table
constraint must
When many foreign key relationships to many tables,
convenient to gather all together as table constraint

31. SQL2 Constraint Types (contd.)
5.Check Constraint
Column or table constraint
Only constraint that forms part of domain and
assertion definition
Specified as search condition
Constraint satisfied if condition returns true

32. SQL2 Constraint Types (contd.)
For small databases constraint names not
necessary
For larger databases ,constraint names must
be given
CHECK constraint in an assertion must have a
name

33. Deferred Constraint Checking
Constraints are checked with every insert ,
update and delete
Deferred constraint checking allows checking
constraints at the completion of a
transaction(COMMIT)
Useful when several updates required at once
to keep database consistent

34. Deferred Constraint Checking(contd.)
At the time of constraint creation two options:
DEFERRABLE: checking can be deferred to
the end of the transaction
NOT DEFERRABLE (default): checking cannot
be deferred.

35. Deferred Constraint Checking(contd.)
For DEFERRABLE:
Initially Immediate (default):starts out as an immediate
constraint
Initially Deferred:starts out as a deferred constraint
Create assertion quota_totals
Check ((offices.quota=sum(salesreps.quota)) and
(salesreps.rep_office=offices.office))
deferrable initially immediate;

36. Deferred Constraint Checking(contd.)
SET CONSTRAINTS
SET CONSTRAINTS :to control the immediate or
deferred processing of constraints
Set constraints quota_totals deferred
Insert………..
Delete………..
Commit
Insert…………..
Initially the constraint will not be checked for first insert
and delete statements,changes will be checked at
commit and quota_totals again is set to deferrable
initially immediate.
Second insert statement would be checked immediately

37. Business Rules
Manager to be notified whenever a customer is
assigned a credit limit of more than Rs.1 lakh
Manager to be notified whenever a Sales
Representative finishes quota assigned
When a new order is taken Qty_in_hand is to be
decreased by the quantity ordered
DBMS responsible for storing,organizing data,ensuring
data integrity.Enforcing business rules is the responsibility
of application programs accessing the database.

38. Business Rules(contd.)
Duplication of effort
Lack of consistency
Maintenance problems
Complexity
Disadvantages of application programs enforcing business rules
Triggers help DBMS in enforcing business
rules

39. Business Rules(contd.)
TRIGGERS
For any event that causes a change in the
database ,
user can specify an action to be carried out by
DBMS
using a trigger
Events that can trigger an action are insert, update and delete
Action triggered by the event is a sequence of SQL statements

40. Business Rules(contd.)
TRIGGERS
Create trigger top_stud_trig
after insert on student
referencing new as newstud
when (marks>95)
insert into highrankers values(newstud.rollno,marks)
for each row
Inserting records in separate highrankers table when marks >95

41. Business Rules(contd.)
TRIGGERS FORMAT
Create [or replace] trigger <trigger_name>
[enable|disable]
<before|after>
<insert|update|delete>
[of <column_name_list>]
On <table_name>
[referencing new as <synonym>]
[for each row]
[when (trigger_condition)]
<trigger_code>

42. Business Rules(contd.)
TRIGGERS - Advantages
Help in enforcing business rules
Useful for enforcing referential integrity
If new data is inconsistent an error can be raised to
rollback the entire transaction

43. Business Rules(contd.)
TRIGGERS - Disadvantages
•Database complexity increases
•Hidden rules
•Hidden performance implications

44. THANKS