Constraints

1
Constraints

• B term 2004 lecture 15

2
Keys Primary keys and unique

• CREATE TABLE Student (
• sNumber int, sName varchar (20),
• dept char (2),
• CONSTRAINT key PRIMARY KEY (sNumber),
• CONSTRAINT uniqueName UNIQUE (sName))

3
Unique vs. primary keys

• Attribute values may be null even if they are
  declared unique (primary key attributes should
  not be null).
• We can have any number of unique constraints for
  a table (only one primary key constraint can be
  defined for a table).

4
Foreign Keys Referential Integrity Constraints

• Specified for a table as
• CONSTRAINT ltfkNamegt FOREIGN KEY (lta1Listgt)
  REFERENCES lttableNamegt (lta2Listgt)
• eg FOREIGN KEY R (a, b) REFERENCES S (a, b)
• Requires (a, b) be unique or primary key of S.
• Consider a row in R with values of a as a1, and b
  as b1 where a1, b1 are both non-null. There must
  be a row in S with values for (a, b) as (a1, b1).

5
Maintaining referential integrity
Inserts/Deletes/Updates

• Default reject any modification that violates
  the constraints.
• We can specify other policies for delete/update
  as set null/cascade.
• Eg FOREIGN KEY (name) references Student (sName)
  ON DELETE SET NULL ON UPDATE CASCADE
• SET NULL if the update violates foreign key
  constraint, the foreign key attributes are set to
  null.
• CASCADE if the update violates foreign key
  constraint, the foreign key attributes are
  modified.

6
Constraints on Attributes

• We can specify attributes as NULL or NOT NULL.
  Eg sName varchar (20) NOT NULL
• We can specify CHECK constraints. Eg gender char
  (1) CHECK (gender IN (F, M))
• salary int CHECK (salary gt 60000)
• CONSTRAINT c1 check (salary gt 60000)
• professor varchar (30) CHECK (professor IN
• select pname from professor)
• Constraints are checked only when
  inserting/updating the table.

7
Constraints for a tuple

• CONSTRAINT ltcNamegt CHECK (ltconditiongt)
• Eg
• CREATE TABLE Marks (midTerm int, final int, CHECK
  (midTerm final gt 25))
• Note Constraints are checked only during
  insertion/update

8
Altering Constraints

• ALTER TABLE ADD CONSTRAINT ltcNamegt ltcBodygt
• ALTER TABLE DROP CONSTRAINT ltcNamegt

9
Constraints on the entire relational schema

• Assertions
• CREATE ASSERTION ltassertionNamegt CHECK
  (ltconditiongt)
• Eg CREATE ASSERTION CHECK (
• NOT EXISTS (SELECT
• FROM PROFESSOR
• WHERE salary lt 60000))
• Condition is any condition that can appear in
  WHERE clause
• For any database modification, the assertion must
  be true.

10
Triggers (Event, Condition, Action rules)

• We specify triggers as Event, Condition, Action
  rules.
• When event occurs, and condition is satisfied,
  the action is performed.

11
Triggers

• CREATE TRIGGER lttriggerNamegt
• BEFORE AFTER INSERTDELETEUPDATE OF
  ltcolumnListgt ON lttableNamegtltviewNamegt
• REFERENCING OLD AS ltoldNamegt, NEW AS ltnewNamegt
• FOR EACH ROW WHEN (ltconditiongt)
• ltPSM bodygt

12
Triggers example

• CREATE TRIGGER t1
• AFTER INSERT ON Student
• REFERENCING NEW as newTuple
• FOR EACH ROW WHEN (newTuple.professor IS NOT
  NULL)
• BEGIN
• INSERT INTO Professor (pname) values
  (newTuple.professor)
• END
• .
• run

13
Statement Level Triggers

• CREATE TRIGGER t1
• AFTER INSERT ON Student
• REFERENCING NEW as newTable
• BEGIN
• INSERT INTO Professor (pname)
• SELECT professor FROM Student
• where (professor IS NOT NULL AND professor NOT
  IN (SELECT pname FROM Professor))
• END
• .
• run