CPS 430/542 Lecture notes: Indices, and enforcing (key and foreign-key) constraints in relational algebra and SQL


Coverage: [FCDB] §§5.5, 6.6 (pp. 295-300), 7.1-7.2.

Overview

  • indices
  • constraints: PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT
  • maintaining/enforcing referential integrity

Indices

  • what is an index?
  • why do we need indices? book index, for example
  • dense vs. sparse indices
  • creating and dropping indices in SQL
  • multi-attribute indices

Active DB elements

  • constraints and triggers
  • write/define once and executed automatically at appropriate times

Key constraints

  • use PRIMARY KEY
  • use of UNIQUE works the same with two differences:
    • can have many UNIQUE declarations
    • UNIQUE attributes can be NULL
  • how can we enforce key constraints?
    • what is necessary?
    • what helps?
  • index and key declaration in one stroke

Foreign keys

  • a set of attributes which reference a set in another relation which are the key (PRIMARY KEY or UNIQUE; therefore, must exist!)
  • an implementation of referential integrity: declaration of `exactly 1' in the referenced entity set
  • referenced attributes must exist and be declared either UNIQUE or the PRIMARY KEY in the referenced relation
  • one exception to `exactly 1': if foreign key was declared UNIQUE and has a (permissible) value of NULL
  • the foreign key of a relation can consist of an attribute from that relation
  • example:
      University(univC#, address, uname)
      President(name, address, univC#) 
      CREATE TABLE University (
   univC# INT PRIMARY KEY,
   uname VARCHAR(255),
   address VARCHAR(255)
);

CREATE TABLE President (
   name CHAR(30) PRIMARY KEY,
   address VARCHAR(255),
   univC# INT REFERENCES University(univC#) ← must be declared a PRIMARY KEY OR UNIQUE
);

or

CREATE TABLE President (
   name CHAR(30) PRIMARY KEY,
   address VARCHAR(255),
   FOREIGN KEY (univC#) REFERENCES University(univC#) ← must be declared a PRIMARY KEY OR UNIQUE
);

How can we enforce referential integrity?

  • default policy: reject violating modifications
  • cascade policy
  • set-null policy

Default policy

  1. insert a tuple into Studios
  2. update a tuple in Studios
  3. delete a tuple from MovieExec
  4. update a tuple in MovieExec

Cascade policy

  • how else can we handle item (3) and (4) above
  • changes to the referenced attribute(s) are mimicked in the foreign key

Set-null policy

  • how else can we handle item (3) and (4) above
  • changes to the referenced attribute(s) cause the foreign key to be set to NULL
    • 3) delete a tuple from MovieExec
    4) update a tuple in MovieExec

Constraints on attributes

  • DEFAULT constraints
  • not-null constraints

SQL = DDL + DML

References

    [FCDB] J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.
Return Home