/**************************************************************
  NON-NULL CONSTRAINT
  GPA must not be NULL
**************************************************************/

insert into Student values ('432', 'Kevin', null, 1000);

alter table Student modify GPA float not null;

delete from Student where GPA is null;

alter table Student modify GPA float not null;

insert into Student values ('432', 'Kevin', null, 1000);

alter table Student modify GPA float;

/**************************************************************
  PRIMARY KEYS
  Student IDs are unique
**************************************************************/

drop table Student;
create table Student(ID char(3) primary key, name varchar(15), GPA float, sizeHS integer) engine = innodb;
<insert Student data>

insert into Student values ('123', 'Kevin', 3.5, 1000);

insert into Student values (NULL, 'Kevin', 3.5, 1000);

alter table Student modify name varchar(15) primary key;

/**************************************************************
  UNIQUE KEYS
  Students can apply to each campus only once
  and each major only once
**************************************************************/

alter table Apply add constraint unique(ID,location);
alter table Apply add constraint unique(ID,major);
alter table Apply add constraint unique(ID,location,major);

drop table Apply;
create table Apply(ID char(3), location varchar(15), major varchar(15), decision char(1), unique(ID,location), unique(ID,major));

insert into Apply values (123, 'Berkeley', 'CS', null);
insert into Apply values (123, 'Los Angeles', 'EE', null);

insert into Apply values (123, 'Berkeley', 'EE', null);

insert into Apply values (123, null, null, 'Y');
insert into Apply values (123, null, null, 'N');

drop table Apply;
create table Apply(ID char(3), location varchar(15), major varchar(15), decision char(1)) engine = innodb;
<insert Apply data>

/**************************************************************
  REFERENTIAL INTEGRITY CONSTRAINTS (FOREIGN KEYS)
  All IDs and locations in Apply must refer to valid
  students and campuses
**************************************************************/

alter table Apply add constraint C1 foreign key (ID) references Student(ID);

alter table Apply add constraint C2 foreign key (location) references Campus(location);
alter table Campus add constraint unique(location);
alter table Apply add constraint C2 foreign key (location) references Campus(location);

/**************************************************************
  REFERENTIAL INTEGRITY VIOLATIONS
  Insertions or updates in referencing table (Apply)
  Deletions or updates in referenced table (Student, Campus)
  Dropping referenced tables (Student, Campus)
**************************************************************/

insert into Apply values (432, 'Berkeley', 'CS', null);

update Apply set ID = 432 where major = 'CS';
update Apply set ID = 234 where major = 'CS';

delete from Campus where location = "San Diego";

update Campus set location = "Bezerkeley" where location = "Berkeley";

delete from Apply where location = "Santa Cruz";
update Campus set location = "Santa Claus" where location = "Santa Cruz";

drop table Campus;

/**************************************************************
  CASCADE AND SET NULL OPTIONS
  For deletions and updates in referenced table (Student, Campus)
**************************************************************/

alter table Apply drop constraint C2;
alter table Apply drop foreign key C2;
alter table Apply add constraint C2 foreign key (location) references Campus(location) on update cascade;
update Campus set location = "Bezerkeley" where location = "Berkeley";

alter table Apply drop foreign key C1;
alter table Apply add constraint C1 foreign key (ID) references Student(ID) on delete set null;
delete from Student where ID = '234';

/**************************************************************
  INTRA-TABLE R.I., MULTI-ATTRIBUTE R.I., and TRUE CASCADE
**************************************************************/

create table T (A integer, B integer, C integer, primary key (A,B));
insert into T values (1,1,1);
insert into T values (2,1,1);
insert into T values (3,2,1);
insert into T values (4,3,2);
insert into T values (5,4,3);
insert into T values (6,5,4);
insert into T values (7,6,5);
insert into T values (8,7,6);

alter table T add constraint C3 foreign key (B,C) references T(A,B) on delete cascade;
delete from T where A=2;

drop table T;