Solutions

Español

T07.001- Create the following tables:
TA (a int, b int) PK(a)
TB (c int, d int) PK(c) FK(d) >> TA (delete: propagate, update:propagate)
TC (e int, f int) PK(e) FK(f) >> TB (delete:propagate, update: propagate)

create table TA(a int, b int, primary key(a));

create table TB(c int, d int, primary key(c), 
  foreign key (d) references TA(a) on delete cascade on update cascade);

create table TC(e int, f int, primary key(e), 
  foreign key (f) references TB(c) on delete cascade on update cascade);

---

T07.002- Insert the following data
TA(1,10)
TA(2,20)
TA(3,30)
TB(100,1)
TB(200,1)
TB(300,2)
TB(400,NULL)
TC(1000,100)
TC(2000,100)
TC(3000,NULL)

insert into TA values (1,10);
insert into TA values (2,20);
insert into TA values (3,30);
insert into TB values (100,1);
insert into TB values (200,1);
insert into TB values (300,2);
insert into TB values (400,NULL);
insert into TC values (1000,100);
insert into TC values (2000,100);
insert into TC values (3000,NULL);

---

T07.003- Delete TA(2,20) and check the changes that have been produced in the 3 tables

delete from TA where a = 2;

---

T07.004- Update TA(1,10) to TA(15,10) and check the changes in the 3 tables.

update TA set a=15 where a = 1;

---

T07.005- Delete TC(2000,100) and check the changes in the 3 tables.

delete from TC where e=2000;

---

T07.006- Delete TA(3,30) and check the changes in the 3 tables.

delete from TA where a=3;

---

T07.007- Delete TB(100,15) and check the changes in the 3 tables.

delete from TB where c=100;

---

T07.008- Delete TC(3000,NULL) and check the changes in the 3 tables.

delete from TC where e=3000;

---

T07.009- Delete TB(400,NULL) and check the changes in the 3 tables.

delete from TB where c=400;

---

T07.010- Delete TA(15,10) and check the changes in the three tables: Are the 3 tables empty?

delete from TA where a=15;

---

T07.011- Create the tables again:
TA (a int, b int) PK(a)
TB (c int, d int) PK(c) FK(d) >> TA
(delete set null, update set null)
TC (e int, f int) PK(e) FK(f) >> TB
(delete set null, update set null)

drop table TC;
drop table TB;
drop table TA;

create table TA(a int, b int, primary key(a));

create table TB(c int, d int, primary key(c), 
  foreign key (d) references TA(a) 
    on delete set null on update set null);

create table TC(e int, f int, primary key(e), 
  foreign key (f) references TB(c) 
    on delete set null on update set null);

---

T07.012- Fill the tables again:
TA(1,10)
TA(2,20)
TA(3,30)
TB(100,1)
TB(200,1)
TB(300,2)
TB(400,NULL)
TC(1000,100)
TC(2000,100)
TC(3000,NULL)

insert into TA values (1,10);
insert into TA values (2,20);
insert into TA values (3,30);
insert into TB values (100,1);
insert into TB values (200,1);
insert into TB values (300,2);
insert into TB values (400,NULL);
insert into TC values (1000,100);
insert into TC values (2000,100);
insert into TC values (3000,NULL);

---

T07.013- Execute the following statements:
Delete TA(2,20)
Update TA(1,10) to TA(15,10)
Update TB(100,NULL) to TB(150,NULL)
Is there any value in the foreign key distinct to NULL?

delete from TA where a = 2;
update TA set a=15 where a = 1;
update TB set c=150 where c = 100;

---

T07.014- Create the tables again:
TA (a int, b int) PK(a)
TB (c int, d int) PK(c) FK(d) >> TA (delete propagate)
TC (e int, f int) PK(e) FK(f) >> TB (update set null)
TA(1,10)
TA(2,20)
TA(3,30)
TB(100,1)
TB(200,1)
TB(300,2)
TB(400,NULL)
TC(1000,100)
TC(2000,100)
TC(3000,NULL)

drop table TC;
drop table TB;
drop table TA;

create table TA(a int, b int, primary key(a));

create table TB(c int, d int, primary key(c), 
  foreign key (d) references TA(a) 
    on delete cascade);

create table TC(e int, f int, primary key(e), 
  foreign key (f) references TB(c) 
    on update set null);
insert into TA values (1,10);
insert into TA values (2,20);
insert into TA values (3,30);
insert into TB values (100,1);
insert into TB values (200,1);
insert into TB values (300,2);
insert into TB values (400,NULL);
insert into TC values (1000,100);
insert into TC values (2000,100);
insert into TC values (3000,NULL);

---

T07.015- Delete TA(1,10): What happens?

delete from TA where a=1;

---

T07.016- Delete TA(2,20): What happens?

delete from TA where a=2;

---

T07.017- Update TB(100,1) to TB(170,1): what happens?

update TB set c=170 where c=100;

---

T07.018- Try to delete TA(1,10) again: Why now is it possible?

delete from TA where a=1;

---