SQL Data Definition Language II

Event-Driven Constraints

Reactive Constraints

On an Event perform a particular Action.

Example:

CREATE TABLE Teaching (
   ProfId         INTEGER,
   CrsCode        CHAR(6),
   Semester       CHAR(6),
   PRIMARY KEY (CrsCode, Semester),
   FOREIGN KEY (ProfId) REFERENCES Professor
      ON DELETE NO ACTION
      ON UPDATE CASCADE,
   FOREIGN KEY (CrsCode) REFERENCES Course
      ON DELETE SET NULL
      ON UPDATE CASCADE );

Events are Delete or Update.
Actions are No Action, Cascade, or Set null.

The result here is that when a professor is deleted from the Professor table, the tuple is left alone and the delete fails.  This prevents deleting a professor who is teaching.  An action of Cascade would delete any tuples that the professor is teaching if that professor is deleted.

Triggers

Reactive foreign key constraints are not general enough --- suppose the referenced set of attributes are not a candidate key or, for many DBMSs, are not the primary key.  Triggers are a general event/action mechanism.

CREATE TRIGGER specifies an event and associates code --- a stored procedure --- with it.

PostgreSQL example --- verify no one given more than a 5% raise:

CREATE FUNCTION RaiseCheck () RETURNS OPAQUE AS '
   BEGIN
      IF NEW.Salary > 1.05 * OLD.Salary THEN
         -- Excessive salary raise - abort transaction.
         RAISE EXCEPTION ''% given an excessive raise'', OLD.EmpName;
      END IF;
      -- Salary increase OK, proceed with transaction.
      RETURN NEW;
   END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER LimitRaises BEFORE UPDATE ON Employee 
   FOR EACH ROW EXECUTE PROCEDURE RaiseCheck();

What kind of constraint (static, dynamic) does this implement?

Alternatively, the code in the if block could have capped the increase at 5%: NEW.Salary := 1.05 * OLD.Salary;.

Altering a Database's Schema

Examples:

DROP TABLE AllMyLifesWork;   -- Whoops.

DROP DATABASE jillz;

ALTER TABLE People
   ADD COLUMN FavIceCream CHAR(6);

ALTER TABLE People
   ADD FOREIGN KEY (FavIceCream) REFERENCES IceCream (Code);

Views

Views are just virtual tables created by queries (a query returns a table):

CREATE VIEW ProfStud AS
SELECT Te.ProfId AS Prof, Tr.StuID AS Stud
FROM Transcript Tr, Teaching Te
WHERE Tr.CrsCode = Te.CrsCode
   AND Tr.Semester = Te.Semester;

Views in PostgreSQL are read-only.  Obviously, views can restrict tables or combine tables to create new tables.