home *** CD-ROM | disk | FTP | other *** search
/ Power-Programmierung / CD1.mdf / magazine / dbms_mag / 9106 / os2_ri < prev   
Text File  |  1991-05-14  |  14KB  |  471 lines
  1. Listing 1.  Log of Enforcing Data Integrity with Referential Integrity
  2.  
  3. --      /************* Create the neworg table      ******************/
  4. EXEC SQL:   CREATE TABLE neworg
  5.                          (deptnumb   SMALLINT NOT NULL PRIMARY KEY,
  6.                           deptname   VARCHAR(14),
  7.                           manager    SMALLINT,
  8.                           division   VARCHAR(10),
  9.                           location   VARCHAR(13));
  10.  
  11.  OK, sql_rcd = 0,        Execution time =     3.9400 secs
  12.  
  13. --      /*********** Let's put some data in it ************/
  14. EXEC SQL:   INSERT INTO neworg
  15.             VALUES (99, 'Software', NULL, 'Oceanview','Hawaii');
  16.  
  17.  OK, sql_rcd = 0,        Execution time =     0.1500 secs
  18.  
  19. EXEC SQL:   INSERT INTO neworg
  20.             VALUES (90, 'Multimedia', NULL, 'Oceanview','Tahiti');
  21.  
  22.  OK, sql_rcd = 0,        Execution time =     0.0700 secs
  23.  
  24. --      /******* Let's see what we've got so far **********/
  25. EXEC SQL:   SELECT * from neworg;
  26.  
  27.  DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
  28.  --------  --------------  -------  ----------  -------------
  29.  99        Software        NULL     Oceanview   Hawaii
  30.  90        Multimedia      NULL     Oceanview   Tahiti
  31.  
  32.  Total Number of Rows SELECTED = 2
  33.  OK, sql_rcd = 0,        Execution time =     0.0600 secs
  34.  
  35. EXEC SQL:   SELECT * from newstaff;
  36.  
  37.  ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
  38.  ------  ---------  ------  -----  ------  --------  --------
  39.  500     Bob        99      Nerds  5       81000.00     00.00
  40.  501     Dan        99      Nerds  4       75600.00     00.00
  41.  508     Joe        99      Nerds  4       99000.00     00.00
  42.  
  43.  Total Number of Rows SELECTED = 3
  44.  OK, sql_rcd = 0,        Execution time =     0.1500 secs
  45.  
  46. --      /***** Make dept column an FK to PK in neworg *****/
  47. EXEC SQL:   ALTER TABLE newstaff
  48.             FOREIGN KEY keyname1(dept) REFERENCES neworg
  49.             ON DELETE RESTRICT;
  50.  
  51.  OK, sql_rcd = 0,        Execution time =     1.1200 secs
  52.  
  53. --      /***** Insert  data for a valid department ********/
  54. EXEC SQL:   INSERT INTO newstaff
  55.             VALUES (605, 'Newhire', 99, 'Nerds',0, 29000.00, 0.0);
  56.  
  57.  OK, sql_rcd = 0,        Execution time =     0.1200 secs
  58.  
  59. --      /**** Insert  data for a valid department ********/
  60. EXEC SQL:   INSERT INTO newstaff
  61.             VALUES (607, 'Newhire', 90, 'Nerds',0, 29000.00, 0.0);
  62.  
  63.  OK, sql_rcd = 0,        Execution time =     0.1300 secs
  64.  
  65. --      /**** What happens w non-valid department (not in NEWORG?) */
  66. EXEC SQL:   INSERT INTO newstaff
  67.             VALUES (609, 'Newhire', 20, 'Sales',0, 29000.00, 0.0);
  68.  
  69.  ERROR, sql_code = -530
  70.  SQL0530N  The insert or update value of FOREIGN KEY "KEYNAME1" 
  71.  is not equal to some value of the primary key of the parent table.
  72.  
  73. CAPTION: Establishing and testing a referential integrity restraint.
  74.  
  75.  
  76.  
  77.  
  78.  
  79.  
  80.  
  81.  
  82.  
  83.  
  84.  
  85. Listing 2. Log of Enforcing Delete Rules with Referential Integrity  
  86.  
  87. --      /****** The default RESTRICT rule protects dependents ****/
  88. EXEC SQL:   DELETE FROM neworg
  89.             WHERE location = 'Tahiti';
  90.  
  91.  ERROR, sql_code = -532
  92.  SQL0532N  A parent row cannot be deleted because the 
  93.  relationship "KEYNAME1" restricts the deletion.
  94.  
  95. --      /*************   Let's see what data is in our tables ****/
  96. EXEC SQL:   SELECT * from neworg;
  97.  
  98.  DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
  99.  --------  --------------  -------  ----------  -------------
  100.  99        Software        NULL     Oceanview   Hawaii
  101.  90        Multimedia      NULL     Oceanview   Tahiti
  102.  
  103.  Total Number of Rows SELECTED = 2
  104.  OK, sql_rcd = 0,        Execution time =     0.1600 secs
  105.  
  106. EXEC SQL:   SELECT * from newstaff;
  107.  
  108.  ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
  109.  ------  ---------  ------  -----  ------  --------  --------
  110.  500     Bob        99      Nerds  5       81000.00     00.00
  111.  501     Dan        99      Nerds  4       75600.00     00.00
  112.  605     Newhire    99      Nerds  0       29000.00     00.00
  113.  508     Joe        99      Nerds  4       99000.00     00.00
  114.  607     Newhire    90      Nerds  0       29000.00     00.00
  115.  
  116.  Total Number of Rows SELECTED = 5
  117.  OK, sql_rcd = 0,        Execution time =     0.1800 secs
  118.  
  119. --      /**** DROP the referential constraint ***************/
  120. EXEC SQL:   ALTER TABLE newstaff
  121.             DROP FOREIGN KEY keyname1;
  122.  
  123.  OK, sql_rcd = 0,        Execution time =     0.2500 secs
  124.  
  125. --      /**** Change the constraint to SET NULLs ***********/
  126. EXEC SQL:   ALTER TABLE newstaff
  127.             FOREIGN KEY keyname1(dept) REFERENCES neworg
  128.             ON DELETE SET NULL;
  129.  
  130.  OK, sql_rcd = 0,        Execution time =     0.4100 secs
  131.  
  132. --      /***** Commit the change                 **********/
  133. EXEC SQL:   COMMIT;
  134.  
  135.  OK, sql_rcd = 0,        Execution time =     0.1200 secs
  136.  
  137. --      /**** What does referential integrity do now? ***/
  138. EXEC SQL:   DELETE FROM neworg
  139.             WHERE location = 'Tahiti';
  140.  
  141.  OK, sql_rcd = 0,        Execution time =     0.2200 secs
  142.  
  143. --      /** Tahiti department is dropped (the parent row) **/
  144. EXEC SQL:   SELECT * from neworg;
  145.  
  146.  DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
  147.  --------  --------------  -------  ----------  -------------
  148.  99        Software        NULL     Oceanview   Hawaii
  149.  
  150.  Total Number of Rows SELECTED = 1
  151.  OK, sql_rcd = 0,        Execution time =     0.0900 secs
  152.  
  153. --      /****  Tahiti Department is set to NULL in child rows **/
  154. EXEC SQL:   SELECT * from newstaff;
  155.  
  156.  ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
  157.  ------  ---------  ------  -----  ------  --------  --------
  158.  500     Bob        99      Nerds  5       81000.00     00.00
  159.  501     Dan        99      Nerds  4       75600.00     00.00
  160.  605     Newhire    99      Nerds  0       29000.00     00.00
  161.  508     Joe        99      Nerds  4       99000.00     00.00
  162.  607     Newhire    NULL    Nerds  0       29000.00     00.00
  163.  
  164.  Total Number of Rows SELECTED = 5
  165.  OK, sql_rcd = 0,        Execution time =     0.1200 secs
  166.  
  167. --      /************* Let's get back our data  ***************/
  168. EXEC SQL:   ROLLBACK;
  169.  
  170.  OK, sql_rcd = 0,        Execution time =     0.1300 secs
  171.  
  172. --      /**** DROP the referential constraint  ***************/
  173. EXEC SQL:   ALTER TABLE newstaff
  174.             DROP FOREIGN KEY keyname1;
  175.  
  176.  OK, sql_rcd = 0,        Execution time =     0.2200 secs
  177.  
  178. --      /**** Let's change the rule to CASCADE **************/
  179. EXEC SQL:   ALTER TABLE newstaff
  180.             FOREIGN KEY keyname1(dept) REFERENCES neworg
  181.             ON DELETE CASCADE;
  182.  
  183.  OK, sql_rcd = 0,        Execution time =     0.3700 secs
  184.  
  185. --      /***** Commit the change                   **********/
  186. EXEC SQL:   COMMIT;
  187.  
  188.  OK, sql_rcd = 0,        Execution time =     0.1600 secs
  189.  
  190. --      /**** Let's see what referential integrity does now */
  191. EXEC SQL:   DELETE FROM neworg
  192.             WHERE location = 'Hawaii';
  193.  
  194.  OK, sql_rcd = 0,        Execution time =     0.2200 secs
  195.  
  196. --      /***** The parent department for Hawaii is gone *****/
  197. EXEC SQL:   SELECT * from neworg;
  198.  
  199.  DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
  200.  --------  --------------  -------  ----------  -------------
  201.  90        Multimedia      NULL     Oceanview   Tahiti
  202.  
  203.  Total Number of Rows SELECTED = 1
  204.  OK, sql_rcd = 0,        Execution time =     0.1300 secs
  205.  
  206. --      /***** Hawaii employees (the child rows) are gone ***/
  207. EXEC SQL:   SELECT * from newstaff;
  208.  
  209.  ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
  210.  ------  ---------  ------  -----  ------  --------  --------
  211.  607     Newhire    90      Nerds  0       29000.00     00.00
  212.  
  213.  Total Number of Rows SELECTED = 1
  214.  OK, sql_rcd = 0,        Execution time =     0.1200 secs
  215.  
  216. --      /********* Point made. Let's bring back our data. **/
  217. EXEC SQL:   ROLLBACK;
  218.  OK, sql_rcd = 0,        Execution time =     0.1600 secs
  219.  
  220.  
  221.  
  222.  
  223.  
  224.  
  225.  
  226.  
  227.  
  228.  
  229.  
  230.  
  231. Listing 3. Cascaded Delete Rules with Referential Integrity
  232.  
  233. --      /************* Create the spouses table ************/
  234. EXEC SQL:   CREATE TABLE spouses
  235.                          (spouse_name  VARCHAR(20),
  236.                           telephone    VARCHAR(10),
  237.                           empl_id      SMALLINT,
  238.                           FOREIGN KEY  homenum(empl_id) REFERENCES newstaff
  239.                           ON DELETE CASCADE);
  240.  
  241.  OK, sql_rcd = 0,        Execution time =     1.1200 secs
  242.  
  243. --      /******  Insert some data in spouses **************/
  244. EXEC SQL:   INSERT INTO spouses
  245.             VALUES ('Michiko','4152223333',501);
  246.  
  247.  OK, sql_rcd = 0,        Execution time =     0.2900 secs
  248. EXEC SQL:   INSERT INTO spouses
  249.             VALUES ('Jeri','4154445555',500);
  250.  
  251.  OK, sql_rcd = 0,        Execution time =     0.0900 secs
  252.  
  253. --      /********* Let's take stock of what we've got *****/
  254. EXEC SQL:   SELECT * FROM neworg;
  255.  
  256.  DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
  257.  --------  --------------  -------  ----------  -------------
  258.  99        Software        NULL     Oceanview   Hawaii
  259.  90        Multimedia      NULL     Oceanview   Tahiti
  260.  
  261.  Total Number of Rows SELECTED = 2
  262.  
  263.  OK, sql_rcd = 0,        Execution time =     0.4500 secs
  264.  
  265. EXEC SQL:   SELECT * FROM newstaff;
  266.  
  267.  ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
  268.  ------  ---------  ------  -----  ------  --------  --------
  269.  500     Bob        99      Nerds  5       81000.00     00.00
  270.  501     Dan        99      Nerds  4       75600.00     00.00
  271.  605     Newhire    99      Nerds  0       29000.00     00.00
  272.  508     Joe        99      Nerds  4       99000.00     00.00
  273.  607     Newhire    90      Nerds  0       29000.00     00.00
  274.  
  275.  Total Number of Rows SELECTED = 5
  276.  
  277.  OK, sql_rcd = 0,        Execution time =     0.3900 secs
  278.  
  279. EXEC SQL:   SELECT * FROM spouses;
  280.  
  281.  SPOUSE_NAME           TELEPHONE   EMPL_ID
  282.  --------------------  ----------  -------
  283.  Michiko               4152223333  501
  284.  Jeri                  4154445555  500
  285.  
  286.  Total Number of Rows SELECTED = 2
  287.  
  288.  OK, sql_rcd = 0,        Execution time =     0.1200 secs
  289.  
  290. --      /** Watch this: No more Hawaii **************/
  291. EXEC SQL:   DELETE  FROM neworg
  292.             WHERE location='Hawai';
  293.  
  294.  OK, sql_rcd = 0,        Execution time =     0.4000 secs
  295.  
  296. --      /** Let's see the damage created ***********/
  297. EXEC SQL:   SELECT * FROM neworg;
  298.  
  299.  DEPTNUMB  DEPTNAME        MANAGER  DIVISION    LOCATION
  300.  --------  --------------  -------  ----------  -------------
  301.  90        Multimedia      NULL     Oceanview   Tahiti
  302.  
  303.  Total Number of Rows SELECTED = 1
  304.  
  305.  OK, sql_rcd = 0,        Execution time =     0.0700 secs
  306.  
  307. EXEC SQL:   SELECT * FROM newstaff;
  308.  
  309.  ID      NAME       DEPT    JOB    YEARS   SALARY    COMM
  310.  ------  ---------  ------  -----  ------  --------  --------
  311.  607     Newhire    90      Nerds  0       29000.00     00.00
  312.  
  313.  Total Number of Rows SELECTED = 1
  314.  
  315.  OK, sql_rcd = 0,        Execution time =     0.1000 secs
  316.  
  317. EXEC SQL:   SELECT * FROM spouses;
  318.  
  319.  SPOUSE_NAME           TELEPHONE   EMPL_ID
  320.  --------------------  ----------  -------
  321.  
  322.  Total Number of Rows SELECTED = 0
  323.  
  324.  OK, sql_rcd = 0,        Execution time =     0.0600 secs
  325.  
  326. --      /******** Point made. Let's bring back our data. ****/
  327. EXEC SQL:   ROLLBACK;
  328.  
  329.  OK, sql_rcd = 0,        Execution time =     0.7200 secs
  330.  
  331.  
  332.  
  333.  
  334.  
  335.  
  336.  
  337.  
  338.  
  339.  
  340. Listing 4.  SQL Server Referential Integrity via Triggers
  341.  
  342. drop table neworg
  343. go
  344. create table neworg (
  345.    deptnumb int          not null,
  346.    deptname varchar(14)  null,
  347.    manager  int          null,
  348.    division varchar (10) null,
  349.    location varchar (13) null)
  350. go
  351. create unique index Prime on neworg(deptnumb)
  352. go
  353. EXECUTE sp_primarykey neworg, deptnumb
  354. go
  355. drop table newstaff
  356. go
  357. create table newstaff (
  358.   id          int          not null,
  359.   name        varchar (20) null,
  360.   deptnumb    int          null,
  361.   job         varchar (20) null,
  362.   years       int          null,
  363.   salary      money        null,
  364.   comm        money        null)
  365. go
  366. create unique index Prime on newstaff(id)
  367. go
  368. go
  369. EXECUTE sp_primarykey newstaff, id
  370. go
  371. EXECUTE sp_foreignkey newstaff,neworg,deptnumb
  372. go             
  373.  
  374. drop table spouses
  375. go
  376. create table spouses (
  377.   spouse_name varchar(20) not null,
  378.   telephone   varchar(10) null,
  379.   empl_id     int         null)
  380. go
  381. create unique index Prime on spouses(spouse_name)
  382. go
  383. EXECUTE sp_primarykey spouses,spouse_name
  384. go
  385. EXECUTE sp_foreignkey spouses,newstaff,empl_id
  386. go
  387.  
  388. /*--------------------------------------------*/
  389. /* Creation of Deletion Triggers - for neworg */
  390. /*--------------------------------------------*/
  391. /* CASCADE deletion trigger */
  392.  
  393. create trigger delcascadetrig
  394.   on  neworg
  395.   for delete
  396.   as
  397.     delete newstaff
  398.       from newstaff,deleted
  399.      where newstaff.deptnumb = deleted.deptnumb
  400. go 
  401. /* SET NULL deletion trigger */
  402.  
  403. create trigger setnulltrig
  404.   on  neworg
  405.   for delete
  406.   as
  407.     update newstaff
  408.        set newstaff.deptnumb = NULL
  409.       from newstaff,deleted
  410.      where newstaff.deptnumb = deleted.deptnumb
  411. go
  412.  
  413. /* RESTRICT deletion trigger */
  414.  
  415. create trigger restrictrig
  416.   on  neworg
  417.   for delete
  418.   as
  419.     if
  420.        (select count(*)
  421.           from newstaff,deleted
  422.          where newstaff.deptnumb = deleted.deptnumb) > 1
  423.          begin
  424.            rollback transaction
  425.            print "Can't delete: referenced elsewhere"
  426.          end
  427. go
  428.  
  429. /*--------------------------------------------*/
  430. /* Creation of Deletion Triggers - for neworg */
  431. /*--------------------------------------------*/
  432. /* CASCADE deletion trigger */
  433.  
  434. create trigger delcascadetrig
  435.   on  newstaff
  436.   for delete
  437.   as
  438.     delete spouses
  439.       from spouses,deleted
  440.      where spouses.empl_id = deleted.id
  441. go 
  442. /* SET NULL deletion trigger */
  443.  
  444. create trigger setnulltrig
  445.   on  neworg
  446.   for delete
  447.   as
  448.     update spouses
  449.        set spouses.deptnumb = NULL
  450.       from spouses,deleted
  451.      where spouses.empl_id = deleted.id
  452. go
  453.  
  454. /* RESTRICT deletion trigger */
  455.  
  456. create trigger restrictrig
  457.   on  neworg
  458.   for delete
  459.   as
  460.     if
  461.        (select count(*)
  462.           from spouses,deleted
  463.          where spouses.empl_id = deleted.id) > 1
  464.          begin
  465.            rollback transaction
  466.            print "Can't delete; referenced elsewhere"
  467.          end
  468. go
  469.  
  470.  
  471.