Data Integrity

1
Data Integrity

• CIT 381

2
The Many Forms of Data Integrity

• Required data - not null
• Validity checking - domain
• Entity integrity primary key
• Referential integrity foreign key
• Business rules varies checks, triggers,
• Consistency as above

3
Validity Checking
Here one may wish to say a quota must always be
between 0 and 500k. We saw CREATE DOMAIN.
There are other ways SQL-Server uses
Transact-SQL (an SQL dialect) CREATE RULE
quota_limit AS _at_VALUE BETWEEN 0.00 and 500000.0
4
Also Column Checks
A CHECK can be added to a column CREATE TABLE
salesreps( empl_num integer NOT
NULL, age integer CHECK(agegt18), . q
uota currency CHECK((quotagt0.00) AND
(quotalt500000.00)) )
5
Moral of Story (repeated)

• SQL provides some techniques to capture the
  simpler data integrity constraints.
• You cant get everything.
• Use the tools available. They are there for a
  reason.
• Most DBMS software handle them better than you
  could.

6
Entity Integrity

• No table can have two entries for the same
  entity. (Redundancy problems would ensue
  otherwise.)
• PRIMARY KEY enforces this.
• In practice, the primary key cannot be
  duplicated.
• SQL standard requires that primary key attributes
  be declared NOT NULL.
• Informix has more sensible syntax
• CREATE TABLE salesreps(
• empl_num integer PRIMARY KEY,

7
Referential Integrity

• If an entity A refers to another entity B, then
  entity B must be represented in the database.
• Handled by FOREIGN KEY
• If the key to table B appears as an attribute of
  table A, then any value for keyB appearing in
  table A must appear in table B.

8
Ref. Integrity Problems

• What happens if we try to INSERT a salesrep with
  a non-existing office?
• The operation is rejected.
• What if we DELETE an existing office?
• Should salesreps be deleted?
• Should operation be rejected?
• Subject of later lecture

9
Triggers

• Triggers are a procedural way to enforce business
  rules that may not be captured by simple SQL
  constraint mechanisms.
• We will use slides from the DS text end of
  chapter 3.
• Look at the ch3b slides.