-- John Aromando -- SQL Referential Integrity --

use ogcPubLev3
go

--edit table structures for referential integrity.

--fix Customers
alter table Customers 
alter column custnum nvarchar(5) NOT NULL;
go

alter table Customers 
add constraint pkeyCust primary key (custnum); 
go

--fix Titles
alter table Titles 
alter column partnum nvarchar(5) NOT NULL;
go

alter table Titles 
add constraint pkeyTitles primary key (partnum); 
go

--fix Slspers
alter table Slspers 
alter column repid nvarchar(3) NOT NULL;
go

alter table Slspers 
add constraint pkeyReps primary key (repid);
go

--fix Sales
alter table Sales 
alter column ordnum int NOT NULL;
go

alter table Sales 
add constraint pkeySales primary key (ordnum);
go

--test for unmatched records in Sales table
select custname, c.custnum, s.custnum, ordnum,
	s.partnum, t.partnum, bktitle
from Sales as s
	left join Customers as c
		on s.custnum = c.custnum
	left join Titles as t
		on s.partnum = t.partnum
where c.custnum is null
	or t.partnum is null

--remove bad records
delete sales where ordnum in (00199,00200)

--establish referential integrity

alter table Sales
add constraint fkeyCust foreign key (custnum) 
references Customers (custnum);
go

alter table Sales
add constraint fkeyTitles foreign key (partnum) 
references Titles (partnum);
go

alter table Sales
add constraint fkeyReps foreign key (repid) 
references slspers (repid);
go

alter table Customers 
add constraint fkeySReps foreign key (repid)
references slspers (repid); 
go

--test integrity

delete customers where custnum = 9989;
go

delete titles where partnum = 40712;
go

--create order for non existing customer # 123
insert sales
(ordnum,sldate,qty,custnum,partnum,repid)
values
('99','2007-08-01',300,123,40125,'W02');
go

--create order for non existing book # 100
insert sales
(ordnum,sldate,qty,custnum,partnum,repid)
values
('99','2007-08-01',300,9989,100,'W02');
go