Category : Files from Magazines
Archive   : DBMS9106.ZIP
Filename : OS2_RI

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