To Syllabus
Enforcing Integrity
Q1. What kinds of
integrities are there?
A1. 1) Every table must
have a Primary Key. 2) Referential Integrity – every Foreign Key must reference
a Primary Key. 3) Domain Integrity - every data entry should be in a specified
set of possible values.
Q2. When can Referential
Integrity be violated?
A2. Referential Integrity can be violated when a Parent Record is either
DELETED, or UPDATED or when a Child Record is INSERTED. When one of these
transactions occurs then you may have a child record that that has no parent. Remember
the database is usually not meant to be manipulated by humans but by application
programs and when integrity is violated you can have a run errors and even worse
logical errors such double billing, billing the wrong individual or even a
tragic errors, such as arresting the wrong person.
Q3. How de we enforce Referential Integrity?
A3. If you try DELETE a parent record then you should implement one of the policies, namely CASCADE the deletion of children or RESTRICT the deletion of PARENT record(s) or assign a new parent to the child record (DEFAULT), where the default parent may be NULL. The RESTRICT policy is usually supported by vendors and CASCADE by some vendors but assigning a child to a new parent is not supported. If you UPDATE a Primary Key then you should cascade the update also but this not supported. If you INSERT a child record then the RESTRICT policy will not let you INSERT unless the inserted child record refers to some parent record.
Q4 So
how do I enforce referential integrity policy that is not supported by a
vendor?
A4. You
have no choice but to resort to a procedural language, either one that is
internal embedded in the database management system or one that is external and
interfaces with the database.
Q5. If I had
a choice which one should I use internal or external?
A5. Choose internal!
Q6. Why is internal procedural language better
than external to enforce integrity?
A6. Because Integrity is part of the database
and not part of application logic. Integrity should be portable with the
database as a package. Have you ever bought something and after you open the
package you find out you must get other parts to make it work. I’m annoyed even
if I have to run out to get a battery.
Q7. Why do some developers use internal procedural
language to handle application logic and is that a good idea?
A7. A very bad idea! The database should be
independent of the application; the two can talk but not mix!
Q8. Why is it a bad idea to mix application code
and database code as part of the database?
A8. If you mix then portability problems, scaling
problems and maintenance problems are almost always worse. Think of the
database as a self-contained unit that can work on its own and can be moved to
another application without modification.
Q9. Ok I’m convinced that to maintain Integrity
it’s better to use a built in procedural language so that integrity enforcement
is part of the database package. Can you give me a lesson in at least one
embedded procedural language?
A9. Yes Oracle’s PL/SQL. Below you will learn
the basics of PL/SQL.
Q10. How about MS SQL Server or IBM’s DB2
A10. Sorry not familiar with built in procedural
languages of MS SQL Server or IBM’s DB2. But knowing one makes it much easier
to learn another.
Oracle
PL/SQL (embedded procedural language)
2. If Else Dates Sequence, input
3. loops
8.
Triggers
9. PL/SQL Tables (arrays)
Q11. What
about Domain Integrity how do I handle that?
A11.
If the Domain is a list then you can do CHECK, for example:
CREATE TABLE S
( sNo CHAR(50) PRIMARY KEY,
sName VARCHAR2(20),
status
NUMBER(5) CHECK( status >0 and status <= 30000)
city VARCHAR2(20) CHECK (city IN ( ‘Paris’, ‘London’, ‘Athens’, ‘New
York’, ‘Newark’) ) ) ;
If
the Domain is an infinite set then you can specify a specific data type for that
attribute. If a domain requires some computation then use a build in PROCEDURE
or FUNCTION.
But
CHECK is the one most frequently used.
To Syllabus