Integrity Constraints

1
Integrity Constraints

• What you will learn from this lecture
• Domain Constraints
• Attribute Constraints
• Relvar Constraints
• Database Constraints
• The Golden Rule
• Referential Integrity
• Assertions
• Triggers

2
Introduction

• The term integrity refers to the accuracy or
  correctness of data in the database.
• Integrity constraints guard against accidental
  damage to the database, by ensuring that
  authorized changes to the database do not result
  in a loss of data consistency.
• A given database might be subject to any number
  of integrity constraints of arbitrary complexity.
• The DBMS needs to be informed of such
  constraints,
• needs to enforce them
  somehow.
• Basically by rejecting any update that would
  otherwise violate them.

3
Introduction (Cont.)

• When a new constrain is declared,
• The system must first make sure the database
  currently satisfies it
• If not, the new constraint is rejected
• Otherwise it is accepted and enforced from that
  point forward.
• We can get rid of existing constraints.
• Declarative integrity is important.
• Few products today provide much in the way of
  such support.
• The opposite approach, i.e.,procedure support,
  using stored or triggered procedures.

4
A Constraint Classification Scheme

• Four broad categories
• Type (Domain) Constrain
• specifies the legal value for a given type.
• Attribute Constrain
• specifies the legal value for a given attribute.
• Relvar Constrain
• specifies the legal value for a given relvar.
• Database Constrain
• specifies the legal value for a given database.

5
Type or Domain Constraints

• Type constraints are the most elementary form of
  integrity constraint.
• A type constraints is an enumeration of the legal
  value of the type, a specification of the values
  that make up the type in question.
• Type constraints are checked immediately.
• No relvar can ever acquire a value of any
  attribute in any tuple that is not the
  appropriate type.

6
Type or Domain Constraints (Cont.)

• The check clause in SQL permits domains to be
  restricted
• Use check clause to ensure that an hourly-wage
  domain allows only values greater than a
  specified value.
• create domain hourly-wage numeric(5,2) constra
  int value-test check(value gt 4.00)
• The domain hourly-wage is declared to be a
  decimal number with 5 digits, 2 of which are
  after the decimal point
• The domain has a constraint that ensures that the
  hourly-wage is greater than 4.00
• The clause constraint value-test is optional
  useful to indicate which constraint an update
  violated.

7
Attribute Constraints

• An attribute constraint is just a declaration to
  the effect that a specified attribute is of a
  specified type.
• For example
• create table account(branch-name char(15),accoun
  t-number char(10) not null,balance integer,)
• Attribute constraints are part of the definition
  of the attribute.
• Any attempt to introduce an attribute value into
  the database that is not a type of the relevant
  type will simply rejected.
• Such a situation should never arise.

8
Relvar Constraints

• A relvar constraint is a constraint on an
  individual relvar.
• For example
• CONSTRAINT SC5
• IS_EMPTY ( S WHERE CITYLONDON AND STATUS!20)
• Relvar constraints are always checked
  immediately.
• Any statement that attempts to assign a value to
  a given relvar that violates any relvar
  constraint for that relvar will effectively just
  be rejected.

9
Database Constraints

• A database constraint is a constraint that
  interrelates two or more distinct relvar.
• For example
• CONSTRAINT DBC1
• IS_EMPTY ( ( S JOIN SP )
• WHERE STATUSlt20 AND QTYgt500)
• In general database constraint checking must be
  deferred to end-of-transaction.
• If a database constraint is violated at COMMIT
  time, the transaction is rolled back.

10
The Golden Rule

• Any given relation has an associated predicate.
• a tuple of that relation denotes true
  proposition.
• A relvar has a predicate.
• all of the possible relations are legal values of
  the relvar.
• example here.
• the predicate for a given relvar serves as the
  criterion for acceptability of updates on the
  relvar.
• whether a particular INSERT or UPDATE or DELETE
  operation on that relvar is allowed or not.

11
The Golden Rule (Cont.)

• Ideally the DBMS would know and understand the
  predicate for every relvar. this goal is
  unachievable.
• No update operation must ever be allowed to leave
  any relvar in a state that violate its own
  predicate.
• applied to all relvar, derived as well as base.
• No update transaction must ever be allowed to
  leave database in a state that violate its own
  predicate.

12
State vs. Transition Constraints

• State constraints concerned with correct states
  of the database.
• Transition constraints on legal transitions from
  one correct state to another correct state.
• For example
• Valid never married to married,
• Not widowed to divorced
• The concept of state vs. transition constraints
  has no meaning for type or attribute constraints.

13
Referential Integrity

• Ensures that a value that appears in one relation
  for a given set of attributes also appears for a
  certain set of attributes in another relation.
• Example If Perryridge is a branch name
  appearing in one of the tuples in the account
  relation, then there exists a tuple in the branch
  relation for branch Perryridge.
• Formal Definition
• Let r1(R1) and r2(R2) be relations with primary
  keys K1 and K2 respectively.
• The subset ? of R2 is a foreign key referencing
  K1 in relation r1, if for every t2 in r2 there
  must be a tuple t1 in r1 such that t1K1
  t2?.
• Referential integrity constraint r2? ? r1k1

14
Referential Integrity in the E-R Model

• Consider relationship set R between entity sets
  E1 and E2. The relational schema for R includes
  the primary keys K1 of E1 and K2 of E2.Then K1
  and K2 form foreign keys on the relational
  schemas for E1 and E2 respectively.
• Weak entity sets are also a source of referential
  integrity constraints. For the relation schema
  for a weak entity set must include the primary
  key of the entity set on which it depends.

15
Database Modification

• The following tests must be made in order to
  preserve the following referential integrity
  constraint
• r2? ? r1k
• Insert. If a tuple t2 is inserted into r2, the
  system must ensure that there is a tuple t1 in r1
  such that t1K t2?. That is
• t2 ? ? r1k
• Delete. If a tuple, t1 is deleted from r1, the
  system must compute the set of tuples in r2 that
  reference t1
• 
  r2 where ? t1K
• If this set is not empty, either the delete
  command is rejected as an error, or the tuples
  that reference t1 must themselves be deleted
  (cascading deletions are possible).

16
Database Modification (Cont.)

• Update. There are two cases
• If a tuple t2 is updated in relation r2 and the
  update modifies values for foreign key ?, then a
  test similar to the insert case is made. Let t2
  denote the new value of tuple t2. The system
  must ensure that
• t2? ? r1k
• If a tuple t1 is updated in r1, and the update
  modifies values for the primary key (K), then a
  test similar to the delete case is made. The
  system must compute
• r2 where ? t1K
• using the old value of t1 (the value before the
  update is applied). If this set is not empty,
  the update may be rejected as an error, or the
  update may be cascaded to the tuples in the set,
  or the tuples in the set may be deleted.

17
Referential Integrity in SQL

• Primary and candidate keys and foreign keys can
  be specified as part of the SQL create table
  statement
• The primary key clause of the create table
  statement includes a list of the attributes that
  comprise the primary key.
• The unique key clause of the create table
  statement includes a list of the attributes that
  comprise a candidate key.
• The foreign key clause of the create table
  statement includes both a list of the attributes
  that comprise the foreign key and the name of the
  relation referenced by the foreign key.

18
Referential Integrity in SQL Example

• create table customer(customer-name char(20) not
  null,customer-street char(30),customer-city char
  (30),primary key (customer-name))
• create table branch(branch-name char(15) not
  null,branch-city char(30),assets integer,primar
  y key (branch-name))

19
Referential Integrity in SQL Example (Cont.)

• create table account(branch-name char(15),accoun
  t-number char(10) not null,balance integer,prima
  ry key (account-number), foreign key
  (branch-name) references branch)
• create table depositor(customer-name char(20)
  not null,account-number char(10) not
  null,primary key (customer-name,
  account-number),foreign key (account-number)
  references account,foreign key (customer-name)
  references customer)

20
Cascading Actions in SQL

• create table account
• . . . foreign key(branch-name) references
  branch on delete cascade on update
  cascade. . . . )
• Due to the on delete cascade clauses, if a delete
  of a tuple in branch results in
  referential-integrity constraint violation, the
  delete cascades to the account relation,
  deleting the tuple that refers to the branch that
  was deleted.
• Cascading updates are similar.

21
Cascading Actions in SQL (Cont.)

• If there is a chain of foreign-key dependencies
  across multiple relations, with on delete cascade
  specified for each dependency, a deletion or
  update at one end of the chain can propagate
  across the entire chain.
• If a cascading update to delete causes a
  constraint violation that cannot be handled by a
  further cascading operation, the system aborts
  the transaction. As a result, all the changes
  caused by the transaction and its cascading
  actions are undone.

22
Assertions

• An assertion (??) is a predicate expressing a
  condition that we wish the database always to
  satisfy.
• An assertion in SQL takes the form
• create assertion ltassertion-namegt check
  ltpredicategt
• When an assertion is made, the system tests it
  for validity. This testing may introduce a
  significant amount of overhead hence assertions
  should be used with great care.

23
Assertion Example

• The sum of all loan amounts for each branch must
  be less than the sum of all account balances at
  the branch.
• create assertion sum-constraint check(not exists
  (select from branch where (select sum(amount)
  from loan where loan.branch-name
  branch.branch-name) gt(select sum(amount) from
  account where loan.branch-name
  branch.branch-name)))

24
Assertion Example

• Every loan has at least one borrower who
  maintains an account with a minimum balance of
  1000.00
• create assertion balance-constraint check(not
  exists (select from loan where not exists (
  select from borrower, depositor,
  account where loan.loan-number
  borrower.loan-number and borrower.customer-name
  depositor.customer-name and
  depositor.account-number account.account-number
  and account.balance gt 1000)))

25
Triggers

• A trigger (???) is a statement that is executed
  automatically by the system as a side effect of a
  modification to the database.
• To design a trigger mechanism, we must
• Specify the conditions under which the trigger is
  to be executed.
• Specify the actions to be taken when the trigger
  executes.
• The SQL standard does not include trigger, but
  many implementations support triggers.

26
Trigger Example

• Suppose that instead of allowing negative account
  balances, the bank deals with overdrafts by
• setting the account balance to zero
• creating a loan in the amount of the overdraft
• giving this loan a loan number identical to the
  account number of the overdrawn account
• The condition for executing the trigger is an
  update to the account relation that results in a
  negative balance value.

27
Trigger Example (Cont.)

• define trigger overdraft on update of account
  T(if new T.balance lt 0then (insert into loan
  values (T.branch-name, T.account-number, new
  T.balance) insert into borrower (select
  customer-name, account-number from
  depositor where T.account-number
  depositor.account-number) update account S set
  S.balance 0 where S.account-number
  T.Account-number))
• The keyword new used before T.balance indicates
  that the value of T.balance after the update
  should be used if it is omitted, the value
  before the update is used.

28
Reading
Chapter 8 (Dates book)