CS 541
Constraints
September 16, 2002
Chris Clifton - CS541 1
Defining a Database Schema
CREATE TABLE name (list of elements).
• Principal elements are attributes and their types, but key
declarations and constraints also appear.
• Similar CREATE X commands for other schema elements X:
views, indexes, assertions, triggers.
• “DROP X name” deletes the created element of kind X with
that name.
Example
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL
);
DROP TABLE Sells;
Fall 2002 Chris Clifton - CS541 2
Constraints
Commercial relational systems allow much more “fine-tuning”
of constraints than do the modeling languages we learned earlier.
• In essence: SQL programming is used to describe
constraints.
Outline
1. Primary key declarations.
2. Foreign-keys = referential integrity constraints.
3. Attribute- and tuple-based checks = constraints within
relations.
4. SQL Assertions = global constraints.
Not found in Oracle.
5. Oracle Triggers.
A substitute for assertions.
Fall 2002 Chris Clifton - CS541 3
Declaring Keys
Use PRIMARY KEY or UNIQUE.
• But only one primary key, many UNIQUEs allowed.
• SQL permits implementations to create an index
(data structure to speed access given a key value)
in response to PRIMARY KEY only.
But PostgreSQL and Oracle create indexes for both.
• SQL does not allow nulls in primary key, but
allows them in “unique” columns (which may
have two or more nulls, but not repeated non-null
values).
Fall 2002 Chris Clifton - CS541 4
Declaring Keys
Two places to declare:
1. After an attribute’s type, if the attribute is a
key by itself.
2. As a separate element.
Essential if key is >1 attribute.
Fall 2002 Chris Clifton - CS541 5
Example
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL,
PRIMARY KEY(bar,beer)
);
Fall 2002 Chris Clifton - CS541 6
Example
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL,
UNIQUE(bar,beer)
);
is different than:
CREATE TABLE Sells (
bar CHAR(20) UNIQUE,
beer VARCHAR(20) UNIQUE,
price REAL
);
Fall 2002 Chris Clifton - CS541 7
Other Properties You Can Give to Attributes
1. NOT NULL = every tuple must have a real value
for this attribute.
2. DEFAULT value = a value to use whenever no
other value of this attribute is known.
Example
CREATE TABLE Drinkers (
name CHAR(30) PRIMARY KEY,
addr CHAR(50)
DEFAULT '123 Sesame St',
phone CHAR(16)
);
Fall 2002 Chris Clifton - CS541 8
INSERT INTO Drinkers(name)
VALUES('Sally')
results in the following tuple:
name addr phone
Sally 123 Sesame St. NULL
• Primary key is by default not NULL.
• This insert is legal.
OK to list a subset of the attributes and values for only
this subset.
• But if we had declared
phone CHAR(16) NOT NULL
then the insertion could not be made.
Fall 2002 Chris Clifton - CS541 9
Interesting Defaults
• DEFAULT CURRENT_TIMESTAMP
• SEQUENCE
CREATE SEQUENCE customer_seq;
CREATE TABLE Customer (
customerID INTEGER
DEFAULT nextval('customer_seq'),
name VARCHAR(30)
);
Fall 2002 Chris Clifton - CS541 10
Foreign Keys
In relation R a clause that “attribute A references S(B)”
says that whatever values appear in the A column of R
must also appear in the B column of relation S.
• B must be declared the primary key for S.
Example
CREATE TABLE Beers (
name CHAR(20) PRIMARY KEY,
manf CHAR(20)
);
CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20) REFERENCES Beers(name),
price REAL
);
Fall 2002 Chris Clifton - CS541 11
Alternative: add another element declaring the
foreign key, as:
CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20),
price REAL,
FOREIGN KEY beer REFERENCES
Beers(name)
);
• Extra element essential if the foreign key is more
than one attribute.
Fall 2002 Chris Clifton - CS541 12
What Happens When
a Foreign Key Constraint is Violated?
• Two ways:
1. Insert or update a Sells tuple so it refers to a nonexistent
beer.
Always rejected.
2. Delete or update a Beers tuple that has a beer value
some Sells tuples refer to.
a) Default: reject.
b) Cascade: Ripple changes to referring Sells tuple.
Example
• Delete “Bud.” Cascade deletes all Sells tuples that
mention Bud.
• Update “Bud” to “Budweiser.” Change all Sells tuples
with “Bud” in beer column to be “Budweiser.”
Fall 2002 Chris Clifton - CS541 13
c) Set Null: Change referring tuples to have
NULL in referring components.
Example
• Delete “Bud.” Set-null makes all Sells
tuples with “Bud” in the beer component
have NULL there.
• Update “Bud” to “Budweiser.” Same
change.
Fall 2002 Chris Clifton - CS541 14
Selecting a Policy
Add ON [DELETE, UPDATE] [CASCADE, SET NULL] to declaration of
foreign key.
Example
CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20),
price REAL,
FOREIGN KEY beer REFERENCES Beers(name)
ON DELETE SET NULL
ON UPDATE CASCADE
);
• “Correct” policy is a design decision.
E.g., what does it mean if a beer goes away? What if a beer changes its name?
Fall 2002 Chris Clifton - CS541 15
Attribute-Based Checks
Follow an attribute by a condition that must hold for
that attribute in each tuple of its relation.
• Form: CHECK (condition).
Condition may involve the checked attribute.
Other attributes and relations may be involved, but only
in subqueries.
Oracle: No subqueries allowed in condition.
• Condition is checked only when the associated
attribute changes (i.e., an insert or update occurs).
Fall 2002 Chris Clifton - CS541 16
Example
CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20) CHECK(
beer IN (SELECT name
FROM Beers)
),
price REAL CHECK(
price
CHECK();
Fall 2002 Chris Clifton - CS541 21
Example
No bar may charge an average of more than $5 for beer.
Sells(bar, beer, price)
CREATE ASSERTION NoRipoffBars
CHECK(NOT EXISTS(
SELECT bar
FROM Sells
GROUP BY bar
HAVING 5.0 .
Also, several conditions like INSERT ON Sells can be
connected by OR.
5. FOR EACH ROW can be omitted, with an important
effect: the action is done once for the relation(s)
consisting of all changes.
Fall 2002 Chris Clifton - CS541 26
Notes
• There are two special variables new and old, representing
the new and old tuple in the change.
old makes no sense in an insert, and new makes no sense in a
delete.
• Notice: in WHEN we use new and old without a colon, but in
actions, a preceding colon is needed.
• The action is a PL/SQL statement.
Simplest form: surround one or more SQL statements with BEGIN
and END.
However, select-from-where has a limited form.
Fall 2002 Chris Clifton - CS541 27
• Triggers are part of the database schema,
like tables or views.
• Important Oracle constraint: the action
cannot change the relation that triggers the
action.
Worse, the action cannot even change a relation
connected to the triggering relation by a
constraint, e.g., a foreign-key constraint.
Fall 2002 Chris Clifton - CS541 28
Example
Maintain a list of all the bars that raise their price for
some beer by more than $1.
Sells(bar, beer, price)
RipoffBars(bar)
CREATE TRIGGER PriceTrig
AFTER UPDATE OF price ON Sells
FOR EACH ROW
WHEN(new.price > old.price + 1.00)
BEGIN
INSERT INTO RipoffBars
VALUES(:new.bar);
END;
.
run
Fall 2002 Chris Clifton - CS541 29
CS 541
Views
September 20, 2002
Chris Clifton - CS541 30
Views
An expression that describes
a table without creating it.
• View definition form is:
CREATE VIEW AS ;
Fall 2002 Chris Clifton - CS541 35
Example
The view CanDrink is the set of drinker-beer pairs such that
the drinker frequents at least one bar that serves the beer.
CREATE VIEW CanDrink AS
SELECT drinker, beer
FROM Frequents, Sells
WHERE Frequents.bar = Sells.bar;
Querying Views
Treat the view as if it were a materialized relation.
Example
SELECT beer
FROM CanDrink
WHERE drinker = ‘Sally’;
Fall 2002 Chris Clifton - CS541 36
Semantics of View Use
Example
Fall 2002 Chris Clifton - CS541 37
Compose
Fall 2002 Chris Clifton - CS541 38
Optimize Query
1. Push selections down tree.
2. Eliminate unnecessary projections.
Fall 2002 Chris Clifton - CS541 39
Modification to Views Via Triggers
Oracle allows us to “intercept” a modification to a view through an
instead-of trigger.
Example
Likes(drinker, beer)
Sells(bar, beer, price)
Frequents(drinker, bar)
CREATE VIEW Synergy AS
SELECT Likes.drinker, Likes.beer,
Sells.bar
FROM Likes, Sells, Frequents
WHERE Likes.drinker = Frequents.drinker AND
Likes.beer = Sells.beer AND
Sells.bar = Frequents.bar;
Fall 2002 Chris Clifton - CS541 40
CREATE TRIGGER ViewTrig
INSTEAD OF INSERT ON Synergy
FOR EACH ROW
BEGIN
INSERT INTO Likes VALUES(
:new.drinker, :new.beer);
INSERT INTO Sells(bar, beer)
VALUES(:new.bar, :new.beer);
INSERT INTO Frequents VALUES(
:new.drinker, :new.bar);
END;
.
run
Fall 2002 Chris Clifton - CS541 41
SQL Triggers
• Read in text.
• Some differences, including:
1. The Oracle restriction about not modifying
the relation of the trigger or other relations
linked to it by constraints is not present in
SQL (but Oracle is real; SQL is paper).
2. The action in SQL is a list of (restricted) SQL
statements, not a PL/SQL statement.
Fall 2002 Chris Clifton - CS541 42
PL/SQL
• Oracle’s version of PSM (Persistent, Stored Modules).
Use via sqlplus.
• A compromise between completely procedural programming and
SQL’s very high-level, but limited statements.
• Allows local variables, loops, procedures, examination of
relations one tuple at a time.
• Rough form:
DECLARE
declarations
BEGIN
executable statements
END;
.
run;
• DECLARE portion is optional.
• Dot and run (or a slash in place of run;) are needed to end the
statement and execute it.
Fall 2002 Chris Clifton - CS541 43
Simplest Form: Sequence of Modifications
Likes(drinker, beer)
BEGIN
INSERT INTO Likes
VALUES('Sally', 'Bud');
DELETE FROM Likes
WHERE drinker = 'Fred' AND
beer = 'Miller';
END;
.
run;
Fall 2002 Chris Clifton - CS541 44
Procedures
Stored database objects that use a PL/SQL statement
in their body.
Procedure Declarations
CREATE OR REPLACE PROCEDURE
() AS
BEGIN
END;
.
run;
Fall 2002 Chris Clifton - CS541 45
• Argument list has name-mode-type triples.
Mode: IN, OUT, or IN OUT for read-only,
write-only, read/write, respectively.
Types: standard SQL + generic types like
NUMBER = any integer or real type.
Since types in procedures must match their
types in the DB schema, you should generally
use an expression of the form
relation.attribute %TYPE
to capture the type correctly.
Fall 2002 Chris Clifton - CS541 46
Example
A procedure to take a beer and price and add it to Joe’s menu.
Sells(bar, beer, price)
CREATE PROCEDURE joeMenu(
b IN Sells.beer %TYPE,
p IN Sells.price %TYPE
) AS
BEGIN
INSERT INTO Sells
VALUES('Joe''s Bar', b, p);
END;
.
run;
• Note “run” only stores the procedure; it doesn’t execute the
procedure.
Fall 2002 Chris Clifton - CS541 47
Invoking Procedures
A procedure call may appear in the body of a
PL/SQL statement.
• Example:
BEGIN
joeMenu('Bud', 2.50);
joeMenu('MooseDrool', 5.00);
END;
.
run;
Fall 2002 Chris Clifton - CS541 48
Assignment
Assign expressions to declared variables with :=.
Branches
IF THEN
ELSE
END IF;
• But in nests, use ELSIF in place of ELSE IF.
Loops
LOOP
. . .
EXIT WHEN
. . .
END LOOP;
Fall 2002 Chris Clifton - CS541 49
Queries in PL/SQL
1. Single-row selects allow retrieval into a
variable of the result of a query that is
guaranteed to produce one tuple.
2. Cursors allow the retrieval of many
tuples, with the cursor and a loop used to
process each in turn.
Fall 2002 Chris Clifton - CS541 50
Single-Row Select
• Select-from-where in PL/SQL must have an INTO clause listing
variables into which a tuple can be placed.
• It is an error if the select-from-where returns more than one
tuple; you should have used a cursor.
Example
• Find the price Joe charges for Bud (and drop it on the floor).
Sells(bar, beer, price)
DECLARE
p Sells.price %TYPE;
BEGIN
SELECT price
INTO p
FROM Sells
WHERE bar = 'Joe''s Bar' AND beer = 'Bud';
END;
.
run
Fall 2002 Chris Clifton - CS541 51
Functions (PostgreSQL Version)
Server-side functions can be written in several
languages:
• SQL
• PL/PGSQL
• PL/TCL
• PL/Perl
• C
Fall 2002 Chris Clifton - CS541 52
SQL Functions (PostgreSQL Version)
Like Oracle stored procedures.
CREATE FUNCTION requires the following
information:
• Function name
• Number of function arguments
• Data type of each argument
• Function return type
• Function action
• Language used by the function action
Fall 2002 Chris Clifton - CS541 53
Example
• A simple SQL function to convert a temperature
from Fahrenheit to centigrade degrees.
CREATE FUNCTION ftoc(float)
RETURNS float
AS 'SELECT ($1 - 32.0) * 5.0 / 9.0;'
LANGUAGE 'sql';
SELECT ftoc(68);
ftoc
------
20
(1 row)
Fall 2002 Chris Clifton - CS541 54
Functions (Continued)
• SQL functions can return multiple values using
SETOF.
• Function actions can also contain INSERTs,
UPDATEs, and DELETEs as well as multiple
queries separated by semicolons.
• Arguments: $1 is automatically replaced by the
first argument of the function call.
$2 is the second argument, etc.
Fall 2002 Chris Clifton - CS541 55
Example
SQL server-side function to compute a sales tax.
CREATE FUNCTION tax(numeric)
RETURNS numeric
AS 'SELECT ($1 *
0.06::numeric(8,2))::numeric(8,2);'
LANGUAGE 'sql';
SELECT tax(100);
tax
------
6.00
(1 row)
Notice the casts to NUMERIC(8,2) using the double-
colon form of type casting, rather than CAST.
Fall 2002 Chris Clifton - CS541 56
Server Side Functions in SQL Queries
CREATE TABLE part (
part_id INTEGER,
name CHAR(10),
cost NUMERIC(8,2),
weight FLOAT
);
INSERT INTO part VALUES (637, 'cable', 14.29, 5);
INSERT INTO part VALUES (638, 'sticker', 0.84, 1);
INSERT INTO part VALUES (639, 'bulb', 3.68, 3);
SELECT part_id, name, cost, tax(cost), cost+tax(cost) AS total
FROM part
ORDER BY part_id;
part_id | name | cost | tax | total
---------+------------+-------+------+-------
637 | cable | 14.29 | 0.86 | 15.15
638 | sticker | 0.84 | 0.05 | 0.89
639 | bulb | 3.68 | 0.22 | 3.90
(3 rows)
Fall 2002 Chris Clifton - CS541 57
Example: Shipping
CREATE FUNCTION shipping(numeric)
RETURNS numeric
AS 'SELECT CASE
WHEN $1 = 2 AND $1 = 4 THEN CAST(6.00 AS numeric(8,2))
END;'
LANGUAGE 'sql';
SELECT part_id, trim(name) AS name, cost, tax(cost), cost+tax(cost)
AS subtotal, shipping(weight),
cost+tax(cost)+shipping(weight) AS total
FROM part
ORDER BY part_id;
part_id | name | cost | tax | subtotal | shipping | total
--------+---------+-------+------+----------+----------+------
637 | cable | 14.29 | 0.86 | 15.15 | 6.00 | 21.15
638 | sticker | 0.84 | 0.05 | 0.89 | 3.00 | 3.89
639 | bulb | 3.68 | 0.22 | 3.90 | 5.00 | 8.90
(3 rows)
Fall 2002 Chris Clifton - CS541 58
Triggers (PostgreSQL Version)
Create a function for states that uses the new
RECORD variable to perform the following
actions:
• Reject a state code that is not exactly two
alphabetic characters
• Reject a state name that contains
nonalphabetic characters
• Reject a state name less than three characters
in length
• Uppercase the state code
• Capitalize the state name
Fall 2002 Chris Clifton - CS541 59
Example Function
CREATE FUNCTION trigger_insert_update_statename()
RETURNS opaque
AS 'BEGIN
IF new.code ! ''[A-Za-z][A-Za-z]$''
THEN RAISE EXCEPTION ''State code must be two alphabetic
characters.'';
END IF;
IF new.name ! ''[A-Za-z ]*$''
THEN RAISE EXCEPTION ''State name must be only alphabetic
characters.'';
END IF;
IF length(trim(new.name)) IS
select-from-where statement
• Cursor gets each tuple from the relation produced by the select-from-
where, in turn, using a fetch statement in a loop.
Fetch statement:
FETCH INTO
variable list;
• Break the loop by a statement of the form:
EXIT WHEN %NOTFOUND;
True when there are no more tuples to get.
• Open and close the cursor with OPEN and CLOSE.
Fall 2002 Chris Clifton - CS541 63
Example
A procedure that examines the menu for Joe’s
Bar and raises by $1.00 all prices that are
less than $3.00.
Sells(bar, beer, price)
• This simple price-change algorithm can be
implemented by a single UPDATE statement,
but more complicated price changes could
not.
Fall 2002 Chris Clifton - CS541 64
CREATE PROCEDURE joeGouge() AS
theBeer Sells.beer%TYPE;
thePrice Sells.price%TYPE;
CURSOR c IS
SELECT beer, price
FROM Sells
WHERE bar = 'Joe''s bar';
BEGIN
OPEN c;
LOOP
FETCH c INTO theBeer, thePrice;
EXIT WHEN c%NOTFOUND;
IF thePrice < 3.00 THEN
UDPATE Sells
SET price = thePrice + 1.00
WHERE bar = 'Joe''s Bar'
AND beer = theBeer;
END IF;
END LOOP;
CLOSE c;
END;
.
run
Fall 2002 Chris Clifton - CS541 65
Row Types
Anything (e.g., cursors, table names) that has
a tuple type can have its type captured with
%ROWTYPE.
• We can create temporary variables that have
tuple types and access their components
with dot.
• Handy when we deal with tuples with many
attributes.
Fall 2002 Chris Clifton - CS541 66
Example
The same procedure with a tuple variable bp.
CREATE PROCEDURE joeGouge() AS
CURSOR c IS
SELECT beer, price
FROM Sells
WHERE bar = 'Joe''s bar';
bp c%ROWTYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO bp;
EXIT WHEN c%NOTFOUND;
IF bp.price < 3.00 THEN
UDPATE Sells
SET price = bp.price + 1.00
WHERE bar = 'Joe''s Bar'
AND beer = bp.beer;
END IF;
END LOOP;
CLOSE c;
END;
.
run
Fall 2002 Chris Clifton - CS541 67