Kleiner Auszug aus SQL The Complete Reference, Data Integrity

[Philipp Hasenfratz]

Halihallo zusammen

Aus "SQL The Complete Reference", s. 292ff

Required data:
Some columns in a database must contain a valid data value in every row; they are not
allowed to contain missing or NULL values. In the sample database, every order must have
an associated customer who placed the oder. Therefore, the CUST column in the ORDERS
table is a required column. The DBMS can be asked to prevent NULL values in this column.

validity checking:
Every column in a database has a domain, a set of data values that are legal for that
column. The sample database uses order numbers that begin at 100,001, so the domain of
the ORDER_NUM column is positive integers greater than 100,000. Similarly, employee
numbers in the EMPL_NUM column must fall within the numeric range of 101 to 999. The
DBMS can be asked to prevent other data values in these columns.

Entity integrity:
The primary key of a table must contain a unique value in each row, which is different
from the values in all other rows. For example, each row of the PRODUCTS table has a
unique set of values in its MFR_ID and PRODUCT_ID columns, which uniquely identifies the
product represented by that row. Duplicate values are illegal, because they wouldn't
allow the database to distinguish one product from another. The DBMS can be asked to
enforce this unique values constraint.

Referential integrity:
A foreign key in a relational database links each row in the child table containing the
foreign key to the row of the parent table containing the matching primary key value. In
the sample database, the value in the REP_OFFICE column of each SALESREPS row links the
salesperson represented by that row to the office where he or she works. The REP_OFFICE
column must contain a valid value from the OFFICE column of the OFFICES table, or the
salesperson will be assigned to an invalid office. The DBMS can be asked to enforce this
foreign key/primary key constraint.

Other data relationships:
The real-world situation modeled by a database will often have additional constraints
that govern the legal data values that may appear in the database. For example, in the
sample database, the sales vice president may want to insure that the quota target for
each office does not exceed the total of the quota targets for the salespeople in that
office. The DBMS can be asked to check modifications to the office and salesperson quota
targets to make sure that their values are constrained in this way.

Business rules:
Updates to a database may be constrained by business rules governing the real-world
transactions that are represented by the updates. For example, the company using the
sample database may have a business rule that forbids accepting an order for which there
is an inadequate product inventory. The DBMS can be asked to check each new row added to
the ORDERS table to make sure that the value in its QTY column does not violate this
business rule.

Consistency:
Many real-world transactions cause multiple updates to a database. For example,
accepting a customer order may involve adding a row to the ORDERS table, increasing the
SALES column in the SALESREPS table for the person who took the order, and increasing
the SALES column in the OFFICES table for the office where that salesperson is assigned.
The INSERT and both UPDATEs must all take place in order for the database to remain in a
consistent, correct state. The DBMS can be asked to enforce this type of consistency
rule or to support applications that implement such rules.

Man möge mir die falschen Interpretationen und Aussagen aufgrund Gedächtnisverlust
verzeihen :-)

Viele Grüsse

Philipp