## Lecture 15 - Transactions, Insert/Update/Dete **Announcements** - Lex14 due on monday at 2PM. - Note that we will drop at least the lowest 3 lecture exercises. - I may add a few optional lecture exercises later this semester, and if so, this number may increase. - Hw #4 due to monday. Expect a new homework soon. ### Transactions - A transaction is any program that changes your data ``` begin transaction ; op1 op2 .. opn commit ; ``` **Transactions are atomic!** - A transaction is a combination of operations treated as a unit - If a transaction commits, the changes made by the transaction should be made permanent, stored in db. - If a transaction aborts, i.e. does not complete, then the db should rollback all changes made by the transaction. A rolled back transaction should not make any changes to the database. create table eliminated_bakers ( baker varchar(10) , episodeid int , comment varchar(100) ) ; --- INSERT insert into eliminated_bakers values ('Jon', 4, 'no comment') ; insert into eliminated_bakers(episodeid, baker) values (5, 'Manon') ; insert into eliminated_bakers(episodeid, baker) values (NULL, 'Rahul') ; insert into eliminated_bakers(episodeid, baker) select r.episodeid , r.baker from results r where r.baker not in ('Manon', 'Jon') and r.result = 'eliminated'; -------DELETE Delete from the relation, all tuples that match the given condition. delete from where ; delete from eliminated_bakers where episodeid >= 6 ; delete from eliminated_bakers where baker like 'L%'; -----UPDATE Update set = where ; Update only the tuples in the relation that satisfy the given condition, for these tuples, set the value of the attribute to the given value. update eliminated_bakers set comment = case when comment is null then '' else comment end || E'\n' || 'Eliminated too early!' where episodeid <=4 ; delete from eliminated_bakers ; drop table eliminated_bakers ; ------COMPLEX UPDATE/DELETE statements create table eliminated_bakers ( baker varchar(10) , episodeid int , comment varchar(100) ) ; insert into eliminated_bakers select baker, episodeid, null from results where result = 'eliminated' ; insert into eliminated_bakers select baker, null, 'Never eliminated, in the final episode' from bakers where baker not in (select baker from results where result = 'eliminated') ; --- Add comment for episodes in which two people were -- eliminated update eliminated_bakers set comment = 'Two bakers were eliminated' where [tuples for the episodes in which 2 bakers are eliminated] update eliminated_bakers set comment = 'Two bakers were eliminated' where episodeid in ( select r.episodeid from results r where r.result = 'eliminated' group by r.episodeid having count(*) > 1 ) ; update eliminated_bakers set comment = '' where comment is null ; update eliminated_bakers set comment = comment || 'Won technical in the same or previous episode' where [this baker did that thing!] update eliminated_bakers set comment = comment || 'Won technical in the same or previous episode' where exists (select * from technicals t where t.rank = 1 and t.baker = eliminated_bakers.baker and (t.episodeid = eliminated_bakers.episodeid or t.episodeid = eliminated_bakers.episodeid-1)) ; update eliminated_bakers e set comment = comment || 'Won technical in the same or previous episode' where exists (select * from technicals t where t.rank = 1 and t.baker = e.baker and (t.episodeid = e.episodeid or t.episodeid = e.episodeid-1)) ; update eliminated_bakers set episodeid = [last episode for this baker] , comment = 'last episode this baker competed in' where episodeid is null ; update eliminated_bakers set episodeid = (select max(t.episodeid) from technicals t where t.baker = eliminated_bakers.baker) , comment = 'last episode this baker competed in' where episodeid is null ; ---- delete from eliminated_bakers where [all people who won star baker] delete from eliminated_bakers where baker in (select baker from results where result = 'star baker') ; ---------- TABLE CONSTRAINTS: Attribute level: -- Not null Table level - Primary key - Unique - Check - Foreign key RIN numeric(9) check (rin >= 660000000 and rin <= 669999999) card varchar(3) check (card in ('Y', 'R', 'Y/R')) create table a (id int primary key , val int ); create table b (id int primary key , aid int , foreign key (aid) references a(id) ); create table c (id int primary key , aid int not null , foreign key (aid) references a(id) ); insert into a values(1, 2); insert into a values(3, 4); insert into a values(5, 8); insert into b values(11, null) ; insert into b values(12, 1) ; insert into b values(13, 1) ; insert into c values(101, 3) ; insert into c values(102, 5) ; insert into c values(103, 5) ; What happens if I delete from table a but other tuples reference the (primary key of the) tuple I am deleting? - RESTRICT option (default). Do not allow the deletion from a - DELETE all referencing tuples [[must be explicitly specified]] - SET NULL to all referencing tuples [[must be explicitly - specified]]a -- All the cascade actions become part of the original transaction. If any of them fail, the whole transaction fails and makes no change. drop table b; drop table c; drop table a ; create table a (id int primary key , val int ); create table b (id int primary key , aid int , foreign key (aid) references a(id) on delete set null on update cascade ); create table c (id int primary key , aid int not null , foreign key (aid) references a(id) on delete cascade on update cascade ); insert into a values(1, 2); insert into a values(3, 4); insert into a values(5, 8); insert into b values(11, null) ; insert into b values(12, 1) ; insert into b values(13, 1) ; insert into b values(14, 5) ; insert into c values(101, 3) ; insert into c values(102, 5) ; insert into c values(103, 5) ;