CS 541 Constraints

1
CS 541Constraints

• September 16, 2002

2
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

3
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
• Primary key declarations.
• Foreign-keys referential integrity constraints.
• Attribute- and tuple-based checks constraints
  within relations.
• SQL Assertions global constraints.
• Not found in Oracle.
• Oracle Triggers.
• A substitute for assertions.

4
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).

5
Declaring Keys

• Two places to declare
• After an attributes type, if the attribute is a
  key by itself.
• As a separate element.
• Essential if key is gt1 attribute.

6
Example

• CREATE TABLE Sells (
• bar CHAR(20),
• beer VARCHAR(20),
• price REAL,
• PRIMARY KEY(bar,beer)
• )

7
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
• )

8
Other Properties You Can Give to Attributes

• NOT NULL every tuple must have a real value for
  this attribute.
• 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)
• )

9

• 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.

10
Interesting Defaults

• DEFAULT CURRENT_TIMESTAMP
• SEQUENCE
• CREATE SEQUENCE customer_seq
• CREATE TABLE Customer (
• customerID INTEGER
• DEFAULT nextval('customer_seq'),
• name VARCHAR(30)
• )

11
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
• )

12

• 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.

13
What Happens Whena Foreign Key Constraint is
Violated?

• Two ways
• Insert or update a Sells tuple so it refers to a
  nonexistent beer.
• Always rejected.
• Delete or update a Beers tuple that has a beer
  value some Sells tuples refer to.
• Default reject.
• 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.

14

• 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.

15
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?

16
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).

17
Example

• CREATE TABLE Sells (
• bar CHAR(20),
• beer CHAR(20) CHECK(
• beer IN (SELECT name
• FROM Beers)
• ),
• price REAL CHECK(
• price lt 5.00
• )
• )
• Check on beer is like a foreign-key constraint,
  except
• The check occurs only when we add a tuple or
  change the beer in an existing tuple, not when we
  delete a tuple from Beers.

18
CS 541Constraints

• September 18, 2002

19
Tuple-Based Checks

• Separate element of table declaration.
• Form like attribute-based check.
• But condition can refer to any attribute of the
  relation.
• Or to other relations/attributes in subqueries.
• Again Oracle forbids the use of subqueries.
• Checked whenever a tuple is inserted or updated.

20
Example

• Only Joe's Bar can sell beer for more than 5.
• CREATE TABLE Sells (
• bar CHAR(20),
• beer CHAR(20),
• price REAL,
• CHECK(bar 'Joe''s Bar' OR
• price lt 5.00)
• )

21
SQL Assertions

• Database-schema constraint.
• Not present in Oracle.
• Checked whenever a mentioned relation changes.
• Syntax
• CREATE ASSERTION lt namegt
• CHECK(ltconditiongt)

22
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 lt AVG(price)
• )
• )
• Checked whenever Sells changes.

23
Example

• There cannot be more bars than drinkers.
• Bars(name, addr, license)
• Drinkers(name, addr, phone)
• CREATE ASSERTION FewBar
• CHECK(
• (SELECT COUNT() FROM Bars) lt
• (SELECT COUNT() FROM Drinkers)
• )
• Checked whenever Bars or Drinkers changes.

24
Triggers (Oracle Version)

• Often called event-condition-action rules.
• Event a class of changes in the DB, e.g.,
  insertions into Beers.
• Condition a test as in a where-clause for
  whether or not the trigger applies.
• Action one or more SQL statements.
• Differ from checks or SQL assertions in that
• Triggers invoked by the event the system doesnt
  have to figure out when a trigger could be
  violated.
• Condition not available in checks.

25
Example

• Whenever we insert a new tuple into Sells, make
  sure the beer mentioned is also mentioned in
  Beers, and insert it (with a null manufacturer)
  if not.
• Sells(bar, beer, price)
• CREATE OR REPLACE TRIGGER BeerTrig
• AFTER INSERT ON Sells
• FOR EACH ROW
• WHEN(new.beer NOT IN
• (SELECT name FROM Beers))
• BEGIN
• INSERT INTO Beers(name)
• VALUES(new.beer)
• END
• .
• run

26
Options

• Can omit OR REPLACE. But if you do, it is an
  error if a trigger of this name exists.
• AFTER can be BEFORE.
• If the relation is a view, AFTER can be INSTEAD
  OF.
• Useful for allowing modifications to a view
  you modify the underlying relations instead.
• INSERT can be DELETE or UPDATE OF ltattributegt.
• Also, several conditions like INSERT ON Sells can
  be connected by OR.
• FOR EACH ROW can be omitted, with an important
  effect the action is done once for the
  relation(s) consisting of all changes.

27
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.

28

• 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.

29
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 gt old.price 1.00)
• BEGIN
• INSERT INTO RipoffBars
• VALUES(new.bar)
• END
• .
• run

30
CS 541Views

• September 20, 2002

31
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

32
Types

• INT or INTEGER.
• REAL or FLOAT.
• CHAR(n) fixed length character string, padded
  with pad characters.
• CHARACTER VARYING (or VARCHAR) (n)
  variable-length strings up to n characters.
• NUMERIC(precision, decimal) is a number with
  precision digits with the decimal point decimal
  digits from the right. NUMERIC(10,2) can store
  99,999,999.99

33

• DATE. SQL form is DATE 'yyyy-mm-dd'
• Oracle uses a different standard format
• Use Alter session command to fix it
• TIME. Form is TIME 'hhmmss.ss' in SQL.
• DATETIME or TIMESTAMP. Form is TIMESTAMP
  'yyyy-mm-dd hhmmss.ss' in SQL.
• Generally preferable to just Date or time
• Be careful with semantics of comparison
• Various extensions
• INTERVAL time interval
• BLOB, LONG, RAW large objects with little typing

34
Changing Columns

• Add an attribute of relation R with
• ALTER TABLE R ADD ltcolumn declarationgt
• Example
• ALTER TABLE Bars ADD phone CHAR(16)
• DEFAULT 'unlisted'
• Columns may also be dropped.
• ALTER TABLE Bars DROP license

35
Views

• An expression that describes
• a table without creating it.
• View definition form is
• CREATE VIEW ltnamegt AS ltquerygt

36
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

37
Semantics of View Use

• Example

38
Compose
39
Optimize Query

• Push selections down tree.
• Eliminate unnecessary projections.

40
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

41

• 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

42
SQL Triggers

• Read in text.
• Some differences, including
• 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).
• The action in SQL is a list of (restricted) SQL
  statements, not a PL/SQL statement.

43
PL/SQL

• Oracles version of PSM (Persistent, Stored
  Modules).
• Use via sqlplus.
• A compromise between completely procedural
  programming and SQLs 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.

44
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

45
Procedures

• Stored database objects that use a PL/SQL
  statement in their body.
• Procedure Declarations
• CREATE OR REPLACE PROCEDURE
• ltnamegt(ltarglistgt) AS
• ltdeclarationsgt
• BEGIN
• ltPL/SQL statementsgt
• END
• .
• run

46

• 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.

47
Example

• A procedure to take a beer and price and add it
  to Joes 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 doesnt
  execute the procedure.

48
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

49
Assignment

• Assign expressions to declared variables with .
• Branches
• IF ltconditiongt THEN
• ltstatement(s)gt
• ELSE
• ltstatement(s)gt
• END IF
• But in nests, use ELSIF in place of ELSE IF.
• Loops
• LOOP
• . . .
• EXIT WHEN ltconditiongt
• . . .
• END LOOP

50
Queries in PL/SQL

• Single-row selects allow retrieval into a
  variable of the result of a query that is
  guaranteed to produce one tuple.
• Cursors allow the retrieval of many tuples, with
  the cursor and a loop used to process each in
  turn.

51
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

52
Functions (PostgreSQL Version)

• Server-side functions can be written in several
  languages
• SQL
• PL/PGSQL
• PL/TCL
• PL/Perl
• C

53
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

54
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)

55
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.

56
Example

• SQL server-side function to compute a sales tax.
  
• CREATE FUNCTION tax(numeric)
• RETURNS numeric
• AS 'SELECT (1 0.06numeric(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.

57
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),
  costtax(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)

58
Example Shipping

• CREATE FUNCTION shipping(numeric)
• RETURNS numeric
• AS 'SELECT CASE
• WHEN 1 lt 2 THEN CAST(3.00 AS
  numeric(8,2))
• WHEN 1 gt 2 AND 1 lt 4 THEN CAST(5.00 AS
  numeric(8,2))
• WHEN 1 gt 4 THEN CAST(6.00 AS
  numeric(8,2))
• END'
• LANGUAGE 'sql'
• SELECT part_id, trim(name) AS name, cost,
  tax(cost), costtax(cost) AS subtotal,
  shipping(weight),costtax(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)

59
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

60
Example Function

• CREATE FUNCTION trigger_insert_update_statename()
  
• RETURNS opaque
• AS 'BEGIN
• IF new.code ! ''A-Za-zA-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)) lt 3
• THEN RAISE EXCEPTION ''State name must longer
  than two characters.''
• END IF
• new.code upper(new.code) -- uppercase
  statename.code
• new.name initcap(new.name) -- capitalize
  statename.name
• RETURN new
• END'
• LANGUAGE 'plpgsql'

61
Trigger (PostgreSQL Version)

• CREATE TRIGGER trigger_statename
• BEFORE INSERT OR UPDATE
• ON statename
• FOR EACH ROW
• EXECUTE PROCEDURE trigger_insert_update_statename(
  )

62
Example Execution

• INSERT INTO statename VALUES ('a', 'alabama')
• ERROR State code must be two alphabetic
  characters.
• INSERT INTO statename VALUES ('al', 'alabama2')
• ERROR State name must be only alphabetic
  characters.
• INSERT INTO statename VALUES ('al', 'al')
• ERROR State name must longer than two
  characters.
• INSERT INTO statename VALUES ('al', 'alabama')
• INSERT 292898 1
• SELECT FROM statename
• code name
• -------------------------------------
• AL Alabama
• (1 row)

63
Cursors

• Declare by
• CURSOR ltnamegt 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 ltcursor namegt INTO
• variable list
• Break the loop by a statement of the form
• EXIT WHEN ltcursor namegt NOTFOUND
• True when there are no more tuples to get.
• Open and close the cursor with OPEN and CLOSE.

64
Example

• A procedure that examines the menu for Joes 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.

65

• CREATE PROCEDURE joeGouge() AS
• theBeer Sells.beerTYPE
• thePrice Sells.priceTYPE
• CURSOR c IS
• SELECT beer, price
• FROM Sells
• WHERE bar 'Joe''s bar'
• BEGIN
• OPEN c
• LOOP
• FETCH c INTO theBeer, thePrice
• EXIT WHEN cNOTFOUND
• IF thePrice lt 3.00 THEN
• UDPATE Sells
• SET price thePrice 1.00
• WHERE bar 'Joe''s Bar'
• AND beer theBeer
• END IF
• END LOOP

66
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.

67
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 cROWTYPE
• BEGIN
• OPEN c
• LOOP
• FETCH c INTO bp
• EXIT WHEN cNOTFOUND
• IF bp.price lt 3.00 THEN
• UDPATE Sells
• SET price bp.price 1.00
• WHERE bar 'Joe''s Bar'
• AND beer bp.beer
• END IF
• END LOOP