Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 4: Ràng buộc cơ sở dữ liệu - Tuấn Nguyên Hoài Đức

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 4: Ràng buộc cơ sở dữ liệu - Tuấn Nguyên Hoài Đức

1. Nội dung trình bày • Đặt vấn đề • Ràng buộc khóa chính • Ràng buộc tồn tại • Ràng buộc duy nhất • Ràng buộc kiểm tra • Ràng buộc mặc nhiên • Đối tượng luật • Quy tắc chung • Trigger Chương IV : Ràng buộc Dữ liệu 2
2. Đặt vấn đề • Mục êu : Chuyển trách nhiệm kiểm tra RBDL cho SQL Server làm một cách tự động mỗi khi có thay đổi trên DL (Con người không cần kiểm tra thủ công) • Cơ chế kiểm tra : • Sẵn có : PK, FK, Unique • Ép thỏa đơn giản : Check, rule, • Ép thỏa phức tạp : Trigger Chương IV : Ràng buộc Dữ liệu 4
3. Ràng buộc khoá chính • Tạo khóa chính ngay trong lúc tạo bảng bằng column constraint. Thích hợp khi PK chỉ gồm 1 thuộc nh Create table HoatDongTiepThi ( MaHoatDong varchar(10) not null Primary key, TenHoatDong nvarchar(50), LoaiHoatDong varchar(10), NgayBatDau DateTime, Khai báo khóa NgayKetThuc DateTime, chính trong Column TongChiPhi float constraint (Xem chương 2 : Xây ) dựng CSDL) Chương IV : Ràng buộc Dữ liệu 6
4. Ràng buộc khoá chính • Tạo khóa chính cho bảng đã có • Đúng nhất là bảng chưa có dữ liệu, nếu bảng đã có dữ liệu, phải xem xét nh trạng CSDL trên tổ hợp thuộc nh muốn chọn làm PK. SQL Server có thể báo lỗi. • Khi chưa có khóa chính thì chỉ cần tạo khóa chính mới. • Thuộc nh khóa chính phải NOT NULL. • Khi đã có khóa chính cũ, cần hủy khóa chính cũ trước khi tạo khóa chính mới. • Alter table Tab_name drop constraint PK_name Chương IV : Ràng buộc Dữ liệu 8
5. Nội dung trình bày • Đặt vấn đề • Ràng buộc khóa chính • Ràng buộc tồn tại • Ràng buộc duy nhất • Ràng buộc kiểm tra • Ràng buộc mặc nhiên • Đối tượng luật • Quy tắc chung • Trigger Chương IV : Ràng buộc Dữ liệu 10
6. Ràng buộc tồn tại • Tạo khóa ngoại ngay trong lúc tạo bảng bằng column constraint. Thích hợp khi FK chỉ gồm 1 thuộc nh Create table ChiTietHoatDong ( MaHoatDong varchar(10) foreign key Khai báo khóa ngoại References HoatDong(MaHoatDong) trong On delete cascade On update cascade, Column constraint STT int, (Xem chương 2 : Xây TenChiTiet nvarchar(10), dựng CSDL) Constraint PK_CTHD primary key(MaHoatDong,STT) ) Phần optional Chương IV : Ràng buộc Dữ liệu 12
7. Ràng buộc tồn tại • Tạo khóa ngoại cho bảng đã có • Đúng nhất là bảng chưa có dữ liệu, nếu bảng đã có dữ liệu, phải xem xét nh trạng CSDL trên tổ hợp thuộc nh muốn chọn làm FK, so sánh với nh trạng CSDL trên tổ hợp thuộc nh làm PK tương ứng bên ref_Tab. SQL Server có thể sẽ báo lỗi • Trường hợp tham chiếu đến tổ hợp thuộc nh không phải khóa ngoại cũng gây báo lỗi. Chương IV : Ràng buộc Dữ liệu 14
8. Ràng buộc tồn tại • Tạo khóa ngoại cho bảng đã có • Ví dụ : Alter table ChiTietHoatDong Add constraint FK_CTHD_HD Foreign key (MaHoatDong) References HoatDong(MaHoatDong) On delete cascade On update cascade Chương IV : Ràng buộc Dữ liệu 16
9. Ràng buộc duy nhất • Tạo RB duy nhất ngay trong lúc tạo bảng bằng column constraint. Create table HoatDongTiepThi ( MaHoatDong varchar(10) not null Primary key, TenHoatDong nvarchar(50) not null unique, LoaiHoatDong varchar(10), NgayBatDau DateTime, NgayKetThuc DateTime, TongChiPhi float Khai báo ràng buộc ) duy nhất trong Column constraint (Xem chương 2 : Xây dựng CSDL) Chương IV : Ràng buộc Dữ liệu 18
10. Ràng buộc duy nhất • Tạo ràng buộc duy nhất cho bảng đã có Alter table Tab_name add constraint U_name unique(colname [ ,n]) • Ví dụ Alter table DanhMucChiPhi add constraint u_TenChiPhi unique(TenChiPhi) Chương IV : Ràng buộc Dữ liệu 20
11. Ràng buộc kiểm tra • Đây là loại ràng buộc dùng để kiểm tra các RBTV miền giá trị một cách tự động. • Cũng như các constraint khác, ràng buộc kiểm tra gắn liền với 1 table cụ thể và dùng để kiểm tra miền giá trị của các thuộc nh trong table đó mỗi khi có thay đổi trên dữ liệu • Ví dụ : Ta có RBTV sau : Điểm thi có giá trị từ 0 đến 10 và chính xác đến 0.25 Chương IV : Ràng buộc Dữ liệu 22
12. Ràng buộc kiểm tra • Tạo ràng buộc kiểm tra ngay khi tạo bảng bằng table constraint Create table KetQua ( MaSV varchar(10) not null, MaMH varchar(10) not null, LanThi int, Diem float, Constraint PK_KQ primary key(MaSV,MaMH), Constraint ck_Diem check (Diem >=0 And Diem <=10 And Khai báo RBKT trong Table Diem*4=Round(Diem*4,0)) constraint (Xem ) chương 2 : Xây dựng CSDL) Chương IV : Ràng buộc Dữ liệu 24
13. Nội dung trình bày • Đặt vấn đề • Ràng buộc khóa chính • Ràng buộc tồn tại • Ràng buộc duy nhất • Ràng buộc kiểm tra • Ràng buộc mặc nhiên • Đối tượng luật • Quy tắc chung • Trigger Chương IV : Ràng buộc Dữ liệu 26
14. Ràng buộc mặc nhiên • Một hình thức của default là gắn vào một table nhất định nào đó. Nó thực sự là một constraint như các constraint đã m hiểu • Ví dụ : Tạo default khi tạo table Create table SanPham ( MaSP varchar(10) primary key, Khai báo TenSP nvarchar(50) not null unique, default trong LuongTon float default 0 column ) constraint (Xem chương 2 : Xây dựng CSDL) Chương IV : Ràng buộc Dữ liệu 28
15. Ràng buộc mặc nhiên • Một hình thức thứ hai : Khác với các constraint đã m hiểu, đối tượng mặc nhiên không phải một constraint • Một constraint luôn định nghĩa trên 1 và chỉ 1 table nhất định • Default không thuộc về một table nào cả, nó là một đối tượng độc lập • Khi muốn default phát huy hiệu lực trên 1 thuộc nh của một bảng, ta phải gắn default vào thuộc nh ấy Chương IV : Ràng buộc Dữ liệu 30
16. Ràng buộc mặc nhiên Tên đối tượng default • Khai báo default Create default def_zero as 0 Create default def_HCM as N’Tp.Hồ Chí Minh’ • Gắn default vào thuộc nh Sp_bindefault ‘def_zero’,’SanPham.LuongTon’ Giá trị đối tượng default Sp_bindefault ‘def_HCM’,’SinhVien.TinhThanh’ Tên đối tượng default Thuộc tính của bảng Tham số truyền dạng chuỗi Tham số truyền dạng chuỗi Chương IV : Ràng buộc Dữ liệu 32
17. Nội dung trình bày • Đặt vấn đề • Ràng buộc khóa chính • Ràng buộc tồn tại • Ràng buộc duy nhất • Ràng buộc kiểm tra • Ràng buộc mặc nhiên • Đối tượng luật • Quy tắc chung • Trigger Chương IV : Ràng buộc Dữ liệu 34
18. Đối tượng luật Create rule rl_0_10_25 as (@GiaTri =0 And Dùng biến @GiaTri*4=Round(@GiaTri*4,0) ) tạm thay Go cho thuộc Sp_bindrule ‘rl_0_10_25’,’KetQua.Diem’ tính cần kiểm tra, Sp_unbindrule ’KetQua.Diem’ tên biến Drop rule rl_0_10_25 tạm luôn phải bắt đầu với 1 ký tự @ Chương IV : Ràng buộc Dữ liệu 36
19. Nội dung trình bày • Đặt vấn đề • Ràng buộc khóa chính • Ràng buộc tồn tại • Ràng buộc duy nhất • Ràng buộc kiểm tra • Ràng buộc mặc nhiên • Đối tượng luật • Quy tắc chung • Trigger Chương IV : Ràng buộc Dữ liệu 38
20. Một số quy tắc chung • Khi tạo constraint về điểm của sinh viên, không bận tậm các điểm hiện có, chỉ quan tâm các điểm sắp được ghi vào DB mà thôi Alter table Ketqua With Nocheck add constraint ck_diem Check(Diem >=0 And Diem <=10 And Diem*4 = Round(Diem*4,0)) Chương IV : Ràng buộc Dữ liệu 40
21. Một số quy tắc chung • Vô hiệu hóa constraint đang có hiệu lực Alter table Tab_name Nocheck constraint {constraint_name [, n]} • Ví dụ : alter table SINHVIEN nocheck constraint u_CMND, chk_Nam Lưu ý : Primary, Unique, Default luôn ở trạng thái hoạt động Chương IV : Ràng buộc Dữ liệu 42
22. Nội dung trình bày • Đặt vấn đề • Ràng buộc khóa chính • Ràng buộc tồn tại • Ràng buộc duy nhất • Ràng buộc kiểm tra • Ràng buộc mặc nhiên • Đối tượng luật • Quy tắc chung • Trigger Chương IV : Ràng buộc Dữ liệu 44
23. Trigger • Inserted : Khi có thay đổi trên DL,có thể sẽ có 1 số dòng được thêm vô, inserted chỉ chứa các dòng này. Cấu trúc giống X. • Deleted : Khi có thay đổi trên DL,có thể sẽ có 1 số dòng bị xóa đi, inserted chỉ chứa các dòng này. Cấu trúc giống X. Chương IV : Ràng buộc Dữ liệu 46
24. Trigger dạng 1 CREATE TRIGGER trigger_name Tên table X ON { table } FOR | AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [DELETE] } AS [BEGIN] Các hành động cần kiểm tra {DETERMINING CONFLICT} IF (CONFLICT) Xử lý, tính toán BEGIN Khi vi phạm RAISERROR (‘Message’,0,1) nhằm xác định thay đổi vừa làm có gây Rollback transacon vi phạm RBTV END đang xét hay không [END] Báo lỗi và hủy hết các thay đổi vừa làm Chương IV : Ràng buộc Dữ liệu 48
25. Trigger dạng 1 • Bảng tầm ảnh hưởng insert update delete DonHang (NgayDatHang) PhieuGH (MaDH,NgayGiaoHang) Chương IV : Ràng buộc Dữ liệu 50
26. Trigger dạng 1 Create trigger trg_DH_PGH On DonHang For update As [Begin] If exists(select * from Insreted I, PhieuGH P Where P.M aDH =I.MaDH And (P.NgayGiaoHang 1)) Begin Raiserror(‘Ngay dat hang khong hop le’,0,1) Rollback transacon End [End] Vi phạm RBTV nên phải hủy bỏ mọi thay đổi, trở về trạng thái ban đầu Chương IV : Ràng buộc Dữ liệu 52
27. Trigger dạng 1 Create trigger trg_DH_PGH On PhieuGH For insert,update As [Begin] If exists(select * from Insreted I, DonHang D Where D.MaDH=I.MaDH And (I.NgayGiaoHang 1)) Begin Raiserror(‘Ngay dat hang khong hop le’,0,1) Rollback transacon End [End] Vi phạm RBTV nên phải hủy bỏ mọi thay đổi, trở về trạng thái ban đầu Chương IV : Ràng buộc Dữ liệu 54
28. Trigger dạng 1 • Cài đặt ràng buộc toàn vẹn sau : Chỉ giao những mặt hàng có đặt. • Phân ch ràng buộc : Chỉ giao những mặt hàng có đặt à mâu thuẫn là khi : Tồn tại một mặt hàng nào đó được giao cho đơn hàng D nhưng lại không được đặt trong D. Đây là mâu thuẫn giữa các chi ết đặt hàng và các chi ết giao hàng tương ứng. Chương IV : Ràng buộc Dữ liệu 56
29. Trigger dạng 1 • Bảng tầm ảnh hưởng insert update delete DonHang PhieuGiao CTPhieuGiao CTDonHang Chương IV : Ràng buộc Dữ liệu 58
30. Trigger dạng 1 • Bảng tầm ảnh hưởng insert update delete DonHang PhieuGiao CTPhieuGiao CTDonHang Chương IV : Ràng buộc Dữ liệu 60
31. Trigger dạng 1 • Bảng tầm ảnh hưởng insert update delete DonHang PhieuGiao CTPhieuGiao CTDonHang Chương IV : Ràng buộc Dữ liệu 62
32. Trigger dạng 1 • Bảng tầm ảnh hưởng insert update delete DonHang PhieuGiao CTPhieuGiao CTDonHang Chương IV : Ràng buộc Dữ liệu 64
33. Trigger dạng 1 • Bảng tầm ảnh hưởng insert update delete DonHang PhieuGiao CTPhieuGiao CTDonHang Chương IV : Ràng buộc Dữ liệu 66
34. Trigger dạng 1 • Bảng tầm ảnh hưởng insert update delete DonHang PhieuGiao INSERTED ? INSERTED CTPhieuGiao DELETED CTDonHang Chương IV : Ràng buộc Dữ liệu 68
35. Trigger dạng 1 • Viết trigger trên bảng CTDonHang • Create trigger trg_PhieuGiao • On CTDonHang for Update,Delete • As • If update(MaDH) Or update(MaMH) • Or not exists(select * from inserted) • If exists (vi phạm) • Begin Select * • Raiserror from Deleted D • Rollbacktransacon Where exists • End (select * from PhieuGiao PG, CTPhieuGiao CTPG where PG.MaDH = D.MaDH And CTPG.MaPG = PG.MaPG And D.MaMH = CTPG.MaMH) Chương IV : Ràng buộc Dữ liệu 70
36. Trigger dạng 2 • Ví dụ : • Cho bảng KetQua(MaSV, MaMH, Diem) • RBTV : Điểm tính chính xác đến 0.25, nếu vi phạm tự động làm tròn thành 0.0/0.25/0.5/0.75 • Giải pháp : Create trigger trg_lamtron On KetQua For insert,update As Update KetQua set Diem = Round(Diem*4,0)/4 Where Diem*4 <> Round(Diem*4,0) GO Chương IV : Ràng buộc Dữ liệu 72
37. Trigger dạng 3 • Ví dụ : • Cho các bảng : • Lop(MaLop, TenLop, Siso, Khoa, KhoaHoc, ) • SinhVien(MaSV, MaLop, HoTen, ) • RBTV : Sĩ số của một lớp phải luôn đúng bằng số sinh viên thực sự thuộc về lớp ấy. • Giải pháp : Viết trigger sau đây Chương IV : Ràng buộc Dữ liệu 74
38. Trigger • Lưu ý : • Bảng ảo Inserted và deleted : trên tinh thần chứa nhiều dòng dữ liệu. • Trigger của SQL Server là After trigger • Ví dụ : • Lop(MaLop, ,SiSoMax) • SinhVien(MaSV, ,MaLop) • Ràng buộc : Số sinh viên thực tế không vượt quá số sinh viên max của lớp. Chương IV : Ràng buộc Dữ liệu 76
39. Trigger • Lưu ý : • Các trigger trên đây thuộc loại DML trigger (Data Manipulation Language Trigger) : Loại trigger dùng để kiểm soát các thao tác cập nhật dữ liệu (thêm, xóa, sửa ). • Ngoài ra còn có những trigger thuộc loại DDL trigger (Data Definition Language Trigger) : Loại trigger kiểm soát các hành động định nghĩa dữ liệu (tạo bảng, xóa bảng ) Chương IV : Ràng buộc Dữ liệu 78
40. • Instead of: • Trigger được gọi thực hiện thay cho thao tác delete/ insert/ update tương ứng • Các dòng mới được thêm chỉ chứa trong bảng inserted • Các dòng bị chỉ định xoá nằm đồng thời trong bảng deleted và bảng dữ liệu (dữ liệu không bị xoá). • Trigger Instead of thường được dùng để xử lý cập nhật trên khung nhìn (view). Chương IV : Ràng buộc Dữ liệu 80
41. Hết chương IV Chương IV : Ràng buộc Dữ liệu 82