Handout 16

The Owner/Property Participation Constraint SQL

Issue: Assuring that owner/property relationship in the DreamHome case maintains the participation constraint. That is, the owner owns property relationship requires full participation by both owners and properities.

Property - since this relation has a foreign key in the owner relation, full participation is guaranteed.

Owner - the following trigger implementation assures that owners without property will be deleted when any property is deleted.

/* drop tables with dependicies first */
Drop Table Property;
Drop Table Owner;

Create Table Owner (
  owner_id Char(4) Primary Key,
  owner_name Char(10),
  owner_phone Char(8)
);

Create Table Property (
  property_id Char(3) Primary Key,
  property_address char(10),
  owner_id Char(4) 
    Constraint fk_owner references Owner(owner_id) 
);

Insert into Owner Values('A00l', 'Alice',   '12345678');
Insert into Owner Values('B002', 'Bill',    '77777777');

Insert into Property Values('P0l', '123 main', 'A00l');
Insert into Property Values('P02', '234 main', 'A00l');
Insert into Property Values('P03', '234 pine', 'B002');
Insert into Property Values('P04', '234 oak', 'B002');

select * from owner;
select * from property;

CREATE OR REPLACE TRIGGER PropertyCounter
AFTER DELETE ON Property
BEGIN
delete from Owner
where 
owner_id NOT IN (
  select owner_id 
  from property 
  group by owner_id);
END;
/

delete from property
where
owner_id = 'A00l'; 

select * from owner;
select * from property;