Constraints

• An active element is an SQL statement written once and stored in the database, ready to be executed at appropriate times.
• Kinds of integrity constraints:
  • key constraints
  • referential (or “foreign-key”) constraints
  • constraints on attributes
  • constraints on tuples
  • interrelation constraints (or “assertions”)
  • triggers

Referential Integrity (Foreign keys constraint)

For these two tables

Studio(name, address, presC#)
MovieExec(name, address, cert#, netWorth)

we can write this CREATE statement

CREATE TABLE Studio (
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    presC# INT REFERENCES MovieExec(cert#)
);

or this

CREATE TABLE Studio (
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    presC# INT,
    FOREIGN KEY (presC#) REFERENCES MovieExec(cert#)
);

Maintaining Referential Integrity

• Referential Integrity is affected by
  1. insertion into the table with foreign key
  2. update of the table with foreign key
  3. deletion from the referenced table
  4. update of the referenced table
• A violating modification of types 1 & 2 can’t be helped—the DBMS will abort it.
• There are 3 distinct policies for a violating modification of types 3 & 4.
  1. Default policy—abort
  2. Cascade policy—delete if delete, update if update
  3. Set-null policy—set the referencing foreign key to NULL

Example

For these two tables

Studio(name, address, presC#)
MovieExec(name, address, cert#, netWorth)

we can write

CREATE TABLE Studio (
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    presC# INT REFERENCES MovieExec(cert#)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Deferred Checking

• Needed when there’s a deadlock—two tables referencing each other through foreign keys. There’s no way to order the table modifications to avoid temporary referential integrity violation.
• SQL solves the problem by allowing temporary violation within a transaction. The checking is deferred until the transaction commits.

• Say for these two tables if both presC# and cert# have been declared to be foreign keys in their respective table.

  Studio(name, address, presC#)
  MovieExec(name, address, cert#, netWorth)

  There’s no way to insert a new tuple each in each table if their foreign keys reference each other. But we can write

  CREATE TABLE Studio (
  name CHAR(30) PRIMARY KEY,
  address VARCHAR(255),
  presC# INT UNIQUE
      REFERENCES MovieEXEC(cert#)
      DEFERRABLE INITIALLY DEFERRED
  );

• Every constraint can be given names. So we can also write

  SET CONSTRAINT MyConstraint DEFERRED;

Constraints on attributes

• NOT NULL constraint. We can write in the declaration of

  Studio(name, address, presC#)

  that

  ...
  presC# INT REFERENCES MovieExec(cert#) NOT NULL

• CHECK constraint. We can write in the declaration of

  Studio(name, address, presC#)

  that

  ...
  presC# INT REFERENCES MovieExec(cert#)
             CHECK (presC# >= 100000)

  or in the declaration of

  MovieStar(name, address, gender, birthdate)

  that

  ...
  gender CHAR(1) CHECK (gender IN ('F', 'M'))

Constraints on tuples

An example:

CREATE TABLE MovieStar (
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    gender CHAR(1),
    birthdate DATE, 
    CHECK (gender 'F' OR name NOT LIKE 'Ms.%'
);

Naming Constraints

• One should always name the constraint so that potential error can be fixed. Examples of how to name constraints are

  ...
  name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY,
  ...

  and

  ...
  gender CHAR(1) CONSTRAINT NoAndro
      CHECK (gender IN ('F', 'M')),
  ...

  and

  ...
  CONSTRAINT RightTitle
      CHECK (gender = 'F' OR name NOT LIKE 'Ms.%');
  ...

Dropping & Adding Constraints

• After table creation, named constraints can be dropped, e.g.,

  ALTER TABLE MovieStar DROP CONSTRAINT NameIsKey;
  ALTER TABLE MovieStar DROP CONSTRAINT NoAndro;
  ALTER TABLE MovieStar DROP CONSTRAINT RightTitle;

• Constraints can also be reinstated, e.g.,

  ALTER TABLE MovieStar ADD CONSTRAINT NameIsKey
      PRIMARY KEY (name);
  ALTER TABLE MovieStar ADD CONSTRAINT NoAndro
      CHECK (gender IN ('F', 'M')),
  ALTER TABLE MovieStar ADD CONSTRAINT RightTitle
      CHECK (gender = 'F' OR name NOT LIKE 'Ms.%');

Assertions

• An assertion is a boolean-valued SQL expression that must be true at all times.

• Here is an example of assertion creation statement. For these two tables

  Studio(name, address, presC#)
  MovieExec(name, address, cert#, netWorth)

  we can create an assertion like this.

  CREATE ASSERTION RichPres CHECK
  (NOT EXISTS
      (SELECT Studio.name
       FROM Studio, MovieExec
       WHERE presC# = cert# AND netWorth < 10000000
      )
  );

Triggers

• A trigger (or event-condition-action rule or ECA rule)
  • is awakened only when specified events occur,
  • tests for the condition when awakened,
  • performs the action associated with the condition if the condition is met.

• Example. For this table

  MovieExec(name, address, cert#, netWorth)

  one can write the trigger

  CREATE TRIGGER NetWorthTrigger
  AFTER UPDATE OF netWorth ON MovieExec
  REFERENCING
  OLD ROW AS OldTuple,
  NEW ROW AS NewTuple
  FOR EACH ROW
  WHEN (OldTuple.netWorth > NewTuple.netWorth)
  UPDATE MovieExec
  SET netWorth = OldTuple.netWorth
  WHERE cert# = NewTuple.cert#;