ARTICLE
Database Integrity
A database is a model of part of the real world. It should be consistent
and its contents should match the section of reality it is modeling.
Data integrity is one of the most important aspects to be taken into
consideration when programming application software. You distinguish
between the following:
Semantic Integrity
Relational Integrity
Operational Integrity
Semantic Integrity
Semantic integrity refers to the consistency and completeness of the
data. In a flight reservation system, for example, the field 'Occupied
seats' for each flight must always contain a value which is less than or
equal to the contents of the field 'Maximum number of seats' - to avoid
overbooked flights. If this is not the case, the data is
inconsistent . In addition, each new booking in the flight
reservations table must increase the contents of the field 'Occupied
seats' for the booked flight in the flights table. Together, both these
operations form a unit and, if only one is executed, the database
becomes incomplete .
In many database systems, you can check semantic integrity conditions
with constraints or triggers. However, since these methods are not used
in an AS ABAP due to their lack
of standardization; programmers of application programs must therefore
verify in an AS ABAP the semantic integrity of the database.
As the flight reservation example shows, the data is usually in an
inconsistent or incomplete state in the course of a
transaction . For this reason, programmers
need to be able to confirm the end of the transaction and thus the
restoration of the semantic integrity of the data. This is achieved
through the database commit .
Relational Integrity
Relational integrity refers to the observation of the integrity rules
governing the relational model. Since data modeling in the
AS ABAP
is based on the relational data model, these rules must be observed.
They include:
Primary key integrity
Each database object must be uniquely identified by a
primary key .
Contrary to relational theory, you can create tables without a primary
key in most database systems. In the ABAP/4 Dictionary , each
table must have a primary key. For this reason, primary key integrity is
automatic.
Value range integrity
Each value of a table field must exist in the value range of this field.
In a flight reservation system, for example, you must be able to
distinguish between private and business customers. This means that the
field 'Business/private customer' can contain only the value 'B'
(for 'business customer') or 'P' (for "private customer").
In most database systems, you can specify value ranges as constraints
when defining a table field. The database system then automatically
prevents the occurrence of an invalid value in a database table.
However, although these methods are not used in the AS ABAP , you
can define value ranges in the ABAP Dictionary for both domains
and database fields, and use them to guarantee the integrity of the
database. In dialog transactions ,
the system uses this knowledge to check user input and to display
possible entries.
Foreign key integrity (referential integrity)
Every foreign key must refer to an
existing primary key. In a flight reservation system, for example, the
contents of the field 'Airline carrier' must exist in the airline
carriers table for each flight.
In most database systems, you can specify
foreign key relationships as a
constraint when defining a table. The database system then automatically
prevents the occurrence of an invalid line in a database table. However,
although these methods are not used in the AS ABAP , you can
define foreign key relationships between tables in the ABAP
Dictionary and use them to guarantee the integrity of the database.
In dialog transactions, the system uses this knowledge to check user
input and to display possible entries.
Operational Integrity
Operational integrity guarantees protection for the dataset against
inconsistencies caused by several users attempting to gain access at the
same time. The necessity for this is demonstrated by the next example,
where clerks <(>S1<)> and <(>S2<)> want to make a booking in the flight
reservation system at the same time.
<(>S1<)> reads the line of the planned flight from the flights table.
The value 'Occupied seats' is less than the value 'Maximum number of
seats'.
<(>S2<)> reads the line of the planned flight from the flights table.
The value 'Occupied seats' is less than the value 'Maximum number of
seats'.
<(>S1<)> makes a new flight reservation.
<(>S1<)> increases 'Occupied seats' by 1.
<(>S2<)> makes a new flight reservation.
<(>S2<)> increases 'Occupied seats' by 1.
Since both clerks find the same value in the field 'Occupied seats', the
flight can be overbooked.
Database systems guarantee operational integrity automatically by
enqueueing competing accesses. This process is achieved through
database locking , which can be
demonstrated by the following variant of the above example:
<(>S1<)> reads the line of the planned flight from the flights table
with the intention to change. The value of 'Occupied seats' is less than
the value of 'Maximum number of seats'.
<(>S2<)> reads the line of the planned flight from the flights table
with the intention to change. Since the line is locked by S1, S2 must
wait.
<(>S1<)> makes a new flight reservation.
<(>S1<)> increases 'Occupied seats' by 1.
<(>S1<)> ends the LUW (Logical Unit of Work) and thus releases the
database locks.
<(>S1<)> can now access the line of the planned flight from the flights
table. The value of 'Occupied seats' is less then the value of 'Maximum
number of seats'.
<(>S2<)> makes a new flight reservation.
<(>S2<)> increases 'Occupied seats' by 1.
<(>S2<)> ends the LUW (Logical Unit of Work) and thus releases the
database locks.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved