ProfitPress Mega CDROM2 …eeware (MSDOS)(1992)(Eng)/ProfitPress-MegaCDROM2.B6I/MAGAZINE/MISC/DBMS9106.ZIP/OS2_RI

Text File | 1991-05-14 | 14.1 KB | 471 lines

Listing 1.  Log of Enforcing Data Integrity with Referential Integrity

--      /************* Create the neworg table      ******************/
EXEC SQL:   CREATE TABLE neworg
                         (deptnumb   SMALLINT NOT NULL PRIMARY KEY,
                          deptname   VARCHAR(14),
                          manager    SMALLINT,
                          division   VARCHAR(10),
                          location   VARCHAR(13));

 OK, sql_rcd = 0,        Execution time =     3.9400 secs

--      /*********** Let's put some data in it ************/
EXEC SQL:   INSERT INTO neworg
            VALUES (99, 'Software', NULL, 'Oceanview','Hawaii');

 OK, sql_rcd = 0,        Execution time =     0.1500 secs

EXEC SQL:   INSERT INTO neworg
            VALUES (90, 'Multimedia', NULL, 'Oceanview','Tahiti');

 OK, sql_rcd = 0,        Execution time =     0.0700 secs

--      /******* Let's see what we've got so far **********/
EXEC SQL:   SELECT * from neworg;

 DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
 --------  --------------  -------  ----------  -------------
 99        Software        NULL     Oceanview   Hawaii
 90        Multimedia      NULL     Oceanview   Tahiti

 Total Number of Rows SELECTED = 2
 OK, sql_rcd = 0,        Execution time =     0.0600 secs

EXEC SQL:   SELECT * from newstaff;

 ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
 ------  ---------  ------  -----  ------  --------  --------
 500     Bob        99      Nerds  5       81000.00     00.00
 501     Dan        99      Nerds  4       75600.00     00.00
 508     Joe        99      Nerds  4       99000.00     00.00

 Total Number of Rows SELECTED = 3
 OK, sql_rcd = 0,        Execution time =     0.1500 secs

--      /***** Make dept column an FK to PK in neworg *****/
EXEC SQL:   ALTER TABLE newstaff
            FOREIGN KEY keyname1(dept) REFERENCES neworg
            ON DELETE RESTRICT;

 OK, sql_rcd = 0,        Execution time =     1.1200 secs

--      /***** Insert  data for a valid department ********/
EXEC SQL:   INSERT INTO newstaff
            VALUES (605, 'Newhire', 99, 'Nerds',0, 29000.00, 0.0);

 OK, sql_rcd = 0,        Execution time =     0.1200 secs

--      /**** Insert  data for a valid department ********/
EXEC SQL:   INSERT INTO newstaff
            VALUES (607, 'Newhire', 90, 'Nerds',0, 29000.00, 0.0);

 OK, sql_rcd = 0,        Execution time =     0.1300 secs

--      /**** What happens w non-valid department (not in NEWORG?) */
EXEC SQL:   INSERT INTO newstaff
            VALUES (609, 'Newhire', 20, 'Sales',0, 29000.00, 0.0);

 ERROR, sql_code = -530
 SQL0530N  The insert or update value of FOREIGN KEY "KEYNAME1"
 is not equal to some value of the primary key of the parent table.

CAPTION: Establishing and testing a referential integrity restraint.











Listing 2. Log of Enforcing Delete Rules with Referential Integrity

--      /****** The default RESTRICT rule protects dependents ****/
EXEC SQL:   DELETE FROM neworg
            WHERE location = 'Tahiti';

 ERROR, sql_code = -532
 SQL0532N  A parent row cannot be deleted because the
 relationship "KEYNAME1" restricts the deletion.

--      /*************   Let's see what data is in our tables ****/
EXEC SQL:   SELECT * from neworg;

 DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
 --------  --------------  -------  ----------  -------------
 99        Software        NULL     Oceanview   Hawaii
 90        Multimedia      NULL     Oceanview   Tahiti

 Total Number of Rows SELECTED = 2
 OK, sql_rcd = 0,        Execution time =     0.1600 secs

EXEC SQL:   SELECT * from newstaff;

 ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
 ------  ---------  ------  -----  ------  --------  --------
 500     Bob        99      Nerds  5       81000.00     00.00
 501     Dan        99      Nerds  4       75600.00     00.00
 605     Newhire    99      Nerds  0       29000.00     00.00
 508     Joe        99      Nerds  4       99000.00     00.00
 607     Newhire    90      Nerds  0       29000.00     00.00

 Total Number of Rows SELECTED = 5
 OK, sql_rcd = 0,        Execution time =     0.1800 secs

--      /**** DROP the referential constraint ***************/
EXEC SQL:   ALTER TABLE newstaff
            DROP FOREIGN KEY keyname1;

 OK, sql_rcd = 0,        Execution time =     0.2500 secs

--      /**** Change the constraint to SET NULLs ***********/
EXEC SQL:   ALTER TABLE newstaff
            FOREIGN KEY keyname1(dept) REFERENCES neworg
            ON DELETE SET NULL;

 OK, sql_rcd = 0,        Execution time =     0.4100 secs

--      /***** Commit the change                 **********/
EXEC SQL:   COMMIT;

 OK, sql_rcd = 0,        Execution time =     0.1200 secs

--      /**** What does referential integrity do now? ***/
EXEC SQL:   DELETE FROM neworg
            WHERE location = 'Tahiti';

 OK, sql_rcd = 0,        Execution time =     0.2200 secs

--      /** Tahiti department is dropped (the parent row) **/
EXEC SQL:   SELECT * from neworg;

 DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
 --------  --------------  -------  ----------  -------------
 99        Software        NULL     Oceanview   Hawaii

 Total Number of Rows SELECTED = 1
 OK, sql_rcd = 0,        Execution time =     0.0900 secs

--      /****  Tahiti Department is set to NULL in child rows **/
EXEC SQL:   SELECT * from newstaff;

 ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
 ------  ---------  ------  -----  ------  --------  --------
 500     Bob        99      Nerds  5       81000.00     00.00
 501     Dan        99      Nerds  4       75600.00     00.00
 605     Newhire    99      Nerds  0       29000.00     00.00
 508     Joe        99      Nerds  4       99000.00     00.00
 607     Newhire    NULL    Nerds  0       29000.00     00.00

 Total Number of Rows SELECTED = 5
 OK, sql_rcd = 0,        Execution time =     0.1200 secs

--      /************* Let's get back our data  ***************/
EXEC SQL:   ROLLBACK;

 OK, sql_rcd = 0,        Execution time =     0.1300 secs

--      /**** DROP the referential constraint  ***************/
EXEC SQL:   ALTER TABLE newstaff
            DROP FOREIGN KEY keyname1;

 OK, sql_rcd = 0,        Execution time =     0.2200 secs

--      /**** Let's change the rule to CASCADE **************/
EXEC SQL:   ALTER TABLE newstaff
            FOREIGN KEY keyname1(dept) REFERENCES neworg
            ON DELETE CASCADE;

 OK, sql_rcd = 0,        Execution time =     0.3700 secs

--      /***** Commit the change                   **********/
EXEC SQL:   COMMIT;

 OK, sql_rcd = 0,        Execution time =     0.1600 secs

--      /**** Let's see what referential integrity does now */
EXEC SQL:   DELETE FROM neworg
            WHERE location = 'Hawaii';

 OK, sql_rcd = 0,        Execution time =     0.2200 secs

--      /***** The parent department for Hawaii is gone *****/
EXEC SQL:   SELECT * from neworg;

 DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
 --------  --------------  -------  ----------  -------------
 90        Multimedia      NULL     Oceanview   Tahiti

 Total Number of Rows SELECTED = 1
 OK, sql_rcd = 0,        Execution time =     0.1300 secs

--      /***** Hawaii employees (the child rows) are gone ***/
EXEC SQL:   SELECT * from newstaff;

 ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
 ------  ---------  ------  -----  ------  --------  --------
 607     Newhire    90      Nerds  0       29000.00     00.00

 Total Number of Rows SELECTED = 1
 OK, sql_rcd = 0,        Execution time =     0.1200 secs

--      /********* Point made. Let's bring back our data. **/
EXEC SQL:   ROLLBACK;
 OK, sql_rcd = 0,        Execution time =     0.1600 secs












Listing 3. Cascaded Delete Rules with Referential Integrity

--      /************* Create the spouses table ************/
EXEC SQL:   CREATE TABLE spouses
                         (spouse_name  VARCHAR(20),
                          telephone    VARCHAR(10),
                          empl_id      SMALLINT,
                          FOREIGN KEY  homenum(empl_id) REFERENCES newstaff
                          ON DELETE CASCADE);

 OK, sql_rcd = 0,        Execution time =     1.1200 secs

--      /******  Insert some data in spouses **************/
EXEC SQL:   INSERT INTO spouses
            VALUES ('Michiko','4152223333',501);

 OK, sql_rcd = 0,        Execution time =     0.2900 secs
EXEC SQL:   INSERT INTO spouses
            VALUES ('Jeri','4154445555',500);

 OK, sql_rcd = 0,        Execution time =     0.0900 secs

--      /********* Let's take stock of what we've got *****/
EXEC SQL:   SELECT * FROM neworg;

 DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
 --------  --------------  -------  ----------  -------------
 99        Software        NULL     Oceanview   Hawaii
 90        Multimedia      NULL     Oceanview   Tahiti

 Total Number of Rows SELECTED = 2

 OK, sql_rcd = 0,        Execution time =     0.4500 secs

EXEC SQL:   SELECT * FROM newstaff;

 ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
 ------  ---------  ------  -----  ------  --------  --------
 500     Bob        99      Nerds  5       81000.00     00.00
 501     Dan        99      Nerds  4       75600.00     00.00
 605     Newhire    99      Nerds  0       29000.00     00.00
 508     Joe        99      Nerds  4       99000.00     00.00
 607     Newhire    90      Nerds  0       29000.00     00.00

 Total Number of Rows SELECTED = 5

 OK, sql_rcd = 0,        Execution time =     0.3900 secs

EXEC SQL:   SELECT * FROM spouses;

 SPOUSE_NAME           TELEPHONE   EMPL_ID
 --------------------  ----------  -------
 Michiko               4152223333  501
 Jeri                  4154445555  500

 Total Number of Rows SELECTED = 2

 OK, sql_rcd = 0,        Execution time =     0.1200 secs

--      /** Watch this: No more Hawaii **************/
EXEC SQL:   DELETE  FROM neworg
            WHERE location='Hawai';

 OK, sql_rcd = 0,        Execution time =     0.4000 secs

--      /** Let's see the damage created ***********/
EXEC SQL:   SELECT * FROM neworg;

 DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
 --------  --------------  -------  ----------  -------------
 90        Multimedia      NULL     Oceanview   Tahiti

 Total Number of Rows SELECTED = 1

 OK, sql_rcd = 0,        Execution time =     0.0700 secs

EXEC SQL:   SELECT * FROM newstaff;

 ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
 ------  ---------  ------  -----  ------  --------  --------
 607     Newhire    90      Nerds  0       29000.00     00.00

 Total Number of Rows SELECTED = 1

 OK, sql_rcd = 0,        Execution time =     0.1000 secs

EXEC SQL:   SELECT * FROM spouses;

 SPOUSE_NAME           TELEPHONE   EMPL_ID
 --------------------  ----------  -------

 Total Number of Rows SELECTED = 0

 OK, sql_rcd = 0,        Execution time =     0.0600 secs

--      /******** Point made. Let's bring back our data. ****/
EXEC SQL:   ROLLBACK;

 OK, sql_rcd = 0,        Execution time =     0.7200 secs










Listing 4.  SQL Server Referential Integrity via Triggers

drop table neworg
go
create table neworg (
   deptnumb int          not null,
   deptname varchar(14)  null,
   manager  int          null,
   division varchar (10) null,
   location varchar (13) null)
go
create unique index Prime on neworg(deptnumb)
go
EXECUTE sp_primarykey neworg, deptnumb
go
drop table newstaff
go
create table newstaff (
  id          int          not null,
  name        varchar (20) null,
  deptnumb    int          null,
  job         varchar (20) null,
  years       int          null,
  salary      money        null,
  comm        money        null)
go
create unique index Prime on newstaff(id)
go
go
EXECUTE sp_primarykey newstaff, id
go
EXECUTE sp_foreignkey newstaff,neworg,deptnumb
go

drop table spouses
go
create table spouses (
  spouse_name varchar(20) not null,
  telephone   varchar(10) null,
  empl_id     int         null)
go
create unique index Prime on spouses(spouse_name)
go
EXECUTE sp_primarykey spouses,spouse_name
go
EXECUTE sp_foreignkey spouses,newstaff,empl_id
go

/*--------------------------------------------*/
/* Creation of Deletion Triggers - for neworg */
/*--------------------------------------------*/
/* CASCADE deletion trigger */

create trigger delcascadetrig
  on  neworg
  for delete
  as
    delete newstaff
      from newstaff,deleted
     where newstaff.deptnumb = deleted.deptnumb
go
/* SET NULL deletion trigger */

create trigger setnulltrig
  on  neworg
  for delete
  as
    update newstaff
       set newstaff.deptnumb = NULL
      from newstaff,deleted
     where newstaff.deptnumb = deleted.deptnumb
go

/* RESTRICT deletion trigger */

create trigger restrictrig
  on  neworg
  for delete
  as
    if
       (select count(*)
          from newstaff,deleted
         where newstaff.deptnumb = deleted.deptnumb) > 1
         begin
           rollback transaction
           print "Can't delete: referenced elsewhere"
         end
go

/*--------------------------------------------*/
/* Creation of Deletion Triggers - for neworg */
/*--------------------------------------------*/
/* CASCADE deletion trigger */

create trigger delcascadetrig
  on  newstaff
  for delete
  as
    delete spouses
      from spouses,deleted
     where spouses.empl_id = deleted.id
go
/* SET NULL deletion trigger */

create trigger setnulltrig
  on  neworg
  for delete
  as
    update spouses
       set spouses.deptnumb = NULL
      from spouses,deleted
     where spouses.empl_id = deleted.id
go

/* RESTRICT deletion trigger */

create trigger restrictrig
  on  neworg
  for delete
  as
    if
       (select count(*)
          from spouses,deleted
         where spouses.empl_id = deleted.id) > 1
         begin
           rollback transaction
           print "Can't delete; referenced elsewhere"
         end
go