INFS3055 1998

Lecture 12 Database integrity (cont)

Oracle

Oracle 7 does not support creation of domains or general assertions, but has 'found' database triggers.

The Oracle SQL syntax allows for definition of primary keys, candidate keys, foreign keys and for record constraints (using the check clause)

on delete cascade is also supported for foreign keys ( but not on update )

The current version of Oracle does not support the create domain or create assertion commands.

Network Model

primary key supported by `DUPLICATES NOT ALLOWED' in key declaration

foreign key supported by the DBTG set structure

Assertions - examples

a domain constraint on city in the supplier table

as a general constraint (SQL/92 style )

   create assertion s1 check
      ( not exists
           ( select * from supplier 
             where city not in (`Sydney', `Melbourne', `Canberra')
           )
      )

/* can't create assertions in current version of Oracle */

or as a base table constraint
```
   create table supplier
   ( S# etc,
     . . . ,
     constraint s1 check (city in ('Sydney', 'Melbourne', 'Canberra'))
   )  
```
/* this can be done in Oracle and, in fact, may be better as a table constraint than as a general constraint, but it is not quite the same as defining, and then using, a new domain. */

or as an Oracle column constraint

   create table supplier
   ( S# etc,
     city varchar(16) constraint s1 
         check (city in ('Sydney', 'Melbourne', 'Canberra'))
   )

a transition constraint in the old Sequel syntax

   assert e1 on update of emp(salary):
      new salary  >  old salary

(can't do this in SQL/92 or in Oracle)

an example of a trigger in the old Sequel syntax

`Cascade delete' on deletion of Supplier record

   define trigger SDel on deletion of Supplier:
      delete Shipment 
      where Shipment.S# = old Supplier.S#

/* no triggers in SQL/92, but cascade delete supported in foreign key constraint */

in create table Shipment

   . . .
   constraint SDel foreign key(S#)
      references Supplier(S#) on delete cascade

/* this is supported in Oracle too */

a foreign key constraint expressed as a general constraint (SQL/92)

  create assertion Ship1 check
  ( not exists
    ( select * from shipment
      where S# not in
      ( select S# from supplier)))

/* Better to use a foreign key table constraint */

a requirement that all shipments from Sydney suppliers must be in quantities >= 100 (SQL/92 general constraint)

  create assertion Sydney_quantity check
  ( not exists
    ( select * from shipment
      where quantity < 100
        and S# in
        ( select S# from supplier where city = 'Sydney')))

Can't do this as a table constraint in Oracle, but you could develop a (complex) database trigger to prevent illegal data from being entered

a (simple) Oracle `database trigger'

Here is a simple Oracle database trigger to accomplish `cascade update' (not yet supported as a table constraint in Oracle). Oracle database triggers are defined using PL/SQL.

  create trigger S#_cascade
  after update of S# on Supplier
  for each row
  begin
    update shipment set S# = :new.S#
    where S# = :old.S#;
  end;

Oracle Column Constraints

in Shipment create table statement

   . . .
   quantity NUMBER check (quantity > 0),

( in some table with a column `State')

   . . .
   State CHAR(20) check (State in (`Tasmania', `Victoria', `New South Wales',
           South Australia', `Queensland'))

( in say the emp table )

   . . .
   Surname CHAR(20) constraint CapSurName
      check (Surname = UPPER(Surname)),

an Oracle Table Constraint (in Emp create table)

   . . .
   check (EndDate  >  StartDate)

/*Note this can not be a column constraint*/

Back to INFS3055 page.

Vicki Peterson
Last modified: Mon May 11 22:06:34 EST 1998
Queries to : help3055@iwaki.anu.edu.au