Complex Integrity Constraints in SQL

1
Complex Integrity Constraints in SQL
2
Constraints over a Single Table

• Table Constraint
• Create TABLE Sailors (sid INTEGER,
• sname CHAR(10),
• rating INTEGER,
• age INTEGER,
• PRIMARY KEY (sid),
• CHECK (rating gt 1 AND rating
  lt10))

3
Example of Table Constraints

• Create TABLE Reserves (sid INTEGER,
• bid INTEGER,
• day DATE,
  FOREIGN KEY (sid) REFERENCES Sailors
• FOREIGN KEY (bid) REFERENCES Boats,
• CONSTRAINT noInterLakeRes
• CHECK (Interlake ltgt
• (SELECT B.bname
• FROM Boats B
• WHERE B.bid Reserves.bid)))

4
Domain Constraints

• CREATE DOMAIN ratingval INTEGER DEFAULT 1
• CHECK (VALUE gt 1 AND VALUE lt10)
• Source type integer
• When creating table Sailors
• rating ratingval
• Problem
• CREATE DOMAIN SailorID INTEGER
• CREATE DOMAIN BoatID INTEGER
• Same source type integer
• Fail to disallow the comparison between
  SailorID and BoatID

5
Distinct Types

• CREATE TYPE sidType AS INTEGER
• CREATE TYPE bidType AS INTEGER
• They are distinct from each other and from the
  source type.

6
ICs over Several Tables

• Create TABLE Sailors (sid INTEGER,
• sname CHAR(10),
• rating INTEGER,
• age INTEGER,
• PRIMARY KEY (sid),
• CHECK (rating gt 1 AND
  rating lt10)
• CHECK ((SELECT COUNT (S.sid) FROM
  Sailors S)
• SELECT COUNT (B.bid) FROM Boats B)
  lt100))
• Problem If the Sailors table is empty, this
  constraint is defined to always hold, which means
  the numbers of tuples in Boats can be anything.

7
Assertions

• CREATE ASSERTION smallClub
• CHECK ((SELECT COUNT (S.sid) FROM Sailors S)
• SELECT COUNT (B.bid) FROM Boats B)
  lt100)
• Advantage not associated with either table.
• Another example
• CREATE ASSERTION TotalPaticipate
• CHECK ((SELECT COUNT (S.sid) FROM Sailors S)
• (SELECT COUNT (DISTINCT R.sid) FROM
  Reserves R))