Constraints and Triggers - RDBMS

This article is meant as a quick lookup of constraints and trigger syntax. Besides most RDBMS's don't even implement the full SQL standard. Sqlite should be able to run below statements.

---------------------------------------------------------------------------
-----------------------------  CONSTRAINTS---------------------------------
---------------------------------------------------------------------------

***************************************************************************
create table Apply(sID int, cName text, major text, decision text, 
check(decision = 'N' or cName <> 'Stanford' or major <> 'CS'));


insert into Apply values(123, 'Stanford', 'CS', 'N');
insert into Apply values(123, 'MIT', 'CS', 'Y');
insert into Apply values(123, 'Stanford', 'CS', 'Y');

***************************************************************************
create table Student(sID int, sName text, GPA real, sizeHS int);

/* check constraints are not supported currently and neither are subqueries in constraints */
create table Apply(sID int, cName text, major text, decision text, 
  check(sID in (select sID from Student))); 
 
/* But this is an example of referential integrity */
  
  
***************************************************************************
/* Using assertions -- Currently not supported by RDBMS's */

create assertion Key
check ((select count(distinct A) from T) = (select count(*) from T)));

create assertion ReferentialIntegrity
check (not exists (select * from Apply where sID not in (select sID from Student)));

check assertion AvgAccept
check (3.0 < (select avg(GPA) from Student where sID in (select sID from Apply where decision = 'Y')));


***************************************************************************
create table College(cName text primary key, state text, enrollment int);
create table Student(sID int primary key, sName text, GPA real, sizeHS int);
create table Apply(sID int references Student(sID), cName text references College(cName), major text, decision text);

//using cascading update
create table Apply(sID int references Student(sID) on delete set null, 
                   cName text references College(cName) on update cascade, 
                   major text, decision text);
***************************************************************************

---------------------------------------------------------------------------
-----------------------------  TRIGGERS  ----------------------------------
---------------------------------------------------------------------------
***************************************************************************
create trigger R1
after insert on Student
for each row
when New.GPA > 3.3 and New.GPA <= 3.6
begin
  insert into Apply values (New.sID, 'Stanford', 'geology', null);
  insert into Apply values (New.sID, 'MIT', 'biology', null);
end;  
***************************************************************************
create trigger R2
after delete on Student
for each row 
begin
  delete from Apply where sID = Old.sID;
end;
***************************************************************************
create trigger R3
after update of cName on College
for each row
begin
  update Apply
  set cName = New.cName
  where cName = Old.cName;
end;
***************************************************************************
create trigger R4
before insert on College
for each row
when exists (select * from College where cName = New.cName)
begin
  select raise(ignore);
end;
***************************************************************************
create trigger R5
before update of cName on College
for each row 
when exists (select * from College where cName = New.cName)
begin
  select raise(ignore);
end;
***************************************************************************
create trigger R6
after insert on Apply
for each row
when (select count(*) from Apply where cName  = New.cName) > 10
begin
  update College set cName = cName || '-Done'
  where cName = New.cName;
end;  

***************************************************************************
create trigger R7
before insert on Student
for each row 
when New.sizeHS < 100 or New.sizeHS > 5000
begin
  select raise(ignore);
end;
***************************************************************************
create trigger AutoAccept
after insert on Apply
for each row 
when (New.cName = 'Berkeley' and
      3.7 < (select GPA from Student where sID = New.sID) and
      1200 < (select sizeHS from Student where sID = New.sID))
begin
  update Apply
  set decision = 'Y'
  where sID = New.sID
  and cName = New.cName;
end;
***************************************************************************
create trigger TooMany
after update of enrollment on College
for each row
when (Old.enrollment <= 16000 and New.enrollment > 16000)
begin
  delete from Apply where cName = New.cName and major = 'EE';
  update Apply
  set decision = 'U'
  where cName = New.cName
  and decision = 'Y';
end;
     

Using sqlite with Firefox sqlite-manager extension

The Stanford database course is using sqlite to experiment with SQL, Constraints, Triggers and so on. As the downloadable executable from this site only has a command line interface I decided to search for a GUI manager for sqlite. I am currently trying the firefox add-on which seems to work just fine.

/* First we create a table with a tuple constraint */
create table Apply(sID int, cName text, major text, decision text, 
check(decision = 'N' or cName <> 'Stanford' or major <> 'CS'));

/* next we try to insert a few tuples but the 3rd one should throw a constraint violation exception */
insert into Apply values(123, 'Stanford', 'CS', 'N');
insert into Apply values(123, 'MIT', 'CS', 'Y');
insert into Apply values(123, 'Stanford', 'CS', 'Y');