Giáo Trình SQL bài 6

1
Ràng buộc (Contraint) và
Toàn vẹn dữ liệu (Data Integrity)
2
I. Giới thiệu toàn vẹn dữ liệu (Data Integrity)
Toàn vẹn dữ liệu là đề cập đến trạng thái của tất cả các giá trị dữ liệu lưu trữ trong CSDL là đúng.
Các table trong CSDL của SQL Server có một số loại toàn vẹn dữ liệu khác nhau. VD: định nghĩa NOT NULL, định nghĩa DEFAULT, thuộc tính IDENTITY, CONTRAINTS, RULES, TRIGGERS, INDEXES.
Xác định đúng Data type của Column hoặc biến (variable) cũng là một cách thúc ép tính toàn vẹn dữ liệu
VD: không thể chấp nhận giá trị của cột TenSinhVien là một giá trị dạng ngày giờ cũng như ngược lại
3
Các loại Ràng buộc 4 loại
Ràng buộc thực thể: đảm bảo rằng một dòng được xác định duy nhất bởi một hoặc nhiều thuộc tính gọi là khóa chính (Primary Key)
Ràng buộc miền trị: đảm bảo rằng giá trị được lưu giữ trong 1 cột phải nằm trong một miền trị hợp lệ được xác định trước. (CHECK)
Ràng buộc tham chiếu: đảm bảo rằng giá trị của khóa ngoại (Foreign Key) được lưu trữ phải là khóa chính (Primary Key) trong Table khác
Ràng buộc do người dùng định nghĩa:
Ràng buộc có thể ở 2 tầm vực: Tầm vực cột và Tầm vực bảng
4
Định nghĩa ràng buộc: Ràng buộc được định nghĩa khi thiết kế cấu trúc table (Create Table) hay hiệu chỉnh table (Alter Table)
Để kiểm tra hoặc xem các toàn vẹn dữ liệu
Sp_HelpConstraint
Hoặc Bật cửa sổ Object Browser của Query Analyzer, mở nhánh Constraint của từng table
Xóa các toàn vẹn dữ liệu
ALTER TABLE
DROP CONSTRAINT
5
II. Tìm hiểu các toàn vẹn dữ liệu.
1. Định nghĩa NULL/ NOT NULL
Một giá trị NULL không đồng nhất với giá trị 0, khoảng trắng, chuỗi rỗng.
NULL có nghĩa là không có thao tác nhập nào thực hiện được.
Việc chỉ định một cột không chấp nhận giá trị Null có thể giúp duy trì tính toàn vẹn dữ liệu.
Nên tránh dùng NULL vì chúng gây ra nhiều phức tạp hơn trong các truy vấn cũng như cập nhật dữ liệu và thay bằng giá trị Default.
6
2. Default Contraint:
Default constraint có thể được tạo tại thời điểm tạo Table, thêm sau khi Table được tạo.
Chì có một giá trị Default có thể được tạo cho một cột.
Các cột TIMESTAMP, IDENTITY và ROWGUIDCOL không thể có default constraint, vì giá trị của chính đã tự động xác định.
Giá trị default có thể là một hằng; một hàm hệ thống; một biến toàn cục, như @@trancount hoặc một hàm do người dùng định nghĩa.
7
Định nghĩa Default constraing trong khi tạo bảng
CREATE TABLE TableName(ColumnName datatype [NULL/NOT NULL]
[CONSTRAINT constraintname] DEFAULT expression

Định nghĩa Default constraint đối với một bảng đã tồn tại.
ALTER TABLE tablename
ADD [CONSTRAINT constraintname] DEFAULT expression FOR columnname
8
Vd1: Định nghĩa default khi tạo table
CREATE TABLE tbl_SinhVien
( MaSV NOT NULL Primary key,
HoTen Nvarchar (100) NOT NULL,
NgayNhapHoc smalldate NOT NULL,
GioiTinh bit Defalut 1 )
Vd2: Định nghĩa default khi table đã tồn tại
Alter Table tbl_SinhVien
Add Default GetDate() NgayNhapHoc_DF For NgayNhapHoc
Hoặc:
ALTER TABLE tbl_SinhVien
Add Constraint NgayNhapHoc_DF Default GetDate() For NgayNhapHoc
9
Đối tượng Default (Default Object)
10
3. Thuộc tính Identity:
Identity là một thuộc tính của Column, nó hẳn không là một constraint. Tuy nhiên, Identity dùng để ràng buộc sự tồn tại dữ liệu.
Một Table chỉ có duy nhất một Column được định nghĩa Identity.
Kiểu dữ liệu của cột Identity phải là kiểu số nguyên.
Giá trị của cột Identity sẽ tự động tăng.
Identity được định nghĩa khi tạo Table, hoặc khi hiệu chỉnh Column bằng lệnh Alter Table
11
VD:
CREATE TABLE tbl_Khoa
( MaKhoa int IDENTITY (1,1) ,
TenKhoa Nvarchar (100) Not Null,
Phone varchar(15) Not Null
)

VD: Khi chèn dữ liệu có định nghĩa Identity
INSERT tbl_Khoa (Ho, Ten)
VALUES (N ‘Công nghệ thông tin’ , ‘0613886699’ )
12
4. Check Constraint:
Check giới hạn các giá trị có thể đưa vào cột. Chúng sẽ xác định các giá trị nào là hợp lệ.
Một cột có thể có nhiều hơn một Check constraint, chúng được lượng giá theo thứ tự được tạo.
Các check constraint giới hạn các giá trị được phép bằng các định nghĩa cho:
+ Một hoặc nhiều vùng các giá trị chấp nhận được.
+ Danh sách các giá trị
+ Một mẫu định trước
Check có thể tham chiếu đến các cột khác trong cùng một Table.
13
Định nghĩa Check Constraint khi tạo Table
Create Table TableName (ColumnName datatype [Constraint ConstraintName]
Check [Not For Replication] (logical_expression)

CREATE TABLE tbl_NhanVien
( MaNV int Primary Key,
HoTen Nvarchar (100) Not Null,
NgayLV int Check (NgayCong> 0 and NgayCong<32) )

CREATE TABLE tbl_Diem
( MaSV char(10) Not Null,
MaMH char(10) Not Null,
Diem float Constraint CK_Diem Check ( Diem>=0 ) )
14
Định nghĩa Check Constraint Table đã tồn tại
ALTER TABLE TableName
[With Check/ with Nocheck] ADD [CONSTRAINT constraintname]
CHECK [Not For Replication] (logical_expression)
Alter Table tbl_ChuVu
Add Constraint CK_NV_HSCV Check
(HSPC >= 0. 1 and HSCV < 0. 5)
Alter Table tbl_BaoHiem
Add Constraint CK_Ngay Check (EndDate > Startdate)
Ghi chú: logical_expression là biểu thức điều kiện dùng giống ĐK trong mệnh đề WHERE như: Not, And, Or, IN, LIKE và các toán tử so sanh...
15
5. Đối tượng Rule (Rule Object)
CREATE RULE RuleName AS
@Variable_Name = condition_expression
VD:
Create Rule range_rule as
@range >= $1000 AND @range < $20000

Create Rule GioiTinh_rule as
@GioiTinh IN (`Nam`, N`Nữ`)

Create Rule SoXe_rule as
@SoXe Like `[0-9][0-9]-[A-Z][0-9]-[0-9][0-9][0-9][0-9]`

Create Rule Diem_rule as
@Diem Between 0 and 10
16
Kết buộc Rule vào một Column
Sp_bindrule‘RuleName’,‘.’
VD:
Sp_bindrule ‘Diem_rule’, ‘tbl_Diem.ThiLan1’

Remove Rule
sp_unbindrule ‘.’
VD:
Sp_unbindrule ‘tbl_Diem.ThiLan1’

Delete Rule
DROP RULE
VD:
DROP RULE Diem_rule
17
Ghi chú về Rule Object:
Ruler là một tính năng tương thích ngược để định nghĩa các quy tắc hợp lệ.
Phải tháo gỡ tất cả các kết buộc (sp_unbindrule) của Rule trước khi xóa rule (Drop Rule)
Một cột chỉ có thể có một Ruler được kết buộc, bạn có thể kết buộc một Ruler với cột đã có định nghĩa Check Constraint. Cả hai đều có giá trị nhưng sẽ ưu tiên Check Constraint.
Rule có thể tham chiếu đến nhiều Column của Table.
Rule chỉ tương tương với Check ở mức độ Column, Rule không phải là một Constraint.
18
6. Primary key Constraint
Một Table chỉ có một constraint Primary key.
SQL Server tự động tạo một chỉ mục (Index) cho Table ứng với các cột tham gia primary key.
Column là Primary key không thể chấp nhận giá trị Null, trùng lắp.
Nếu một Primary key constraint được ấn định trên nhiều cột, các giá trị có thể được lập lại trong một cột, nhưng mỗi sự kết hợp giá trị từ tất cả các cột trong Primary key constraint phải là sự kết hợp duy nhất.
19
a. Tạo Primary key Constraint
Có thể tạo constraint Primary key trong khi tạo Table hoặc thêm constraint Primary key cho table có sẵn.
Để hiệu chỉnh constraint Primary key của một Table thì bạn phải xóa constraint Primary key và tạo lại.
Đối với Column được tạo mà chấp nhận giá trị NULL khi hiệu chỉnh cột đó là giá Primary Key thì ta phải hiệu chỉnh NOT NULL trước sau đó mới gán cột đó là Primary Key và giá trị của cột nếu có không được trùng lắp.
20
Định nghĩa primary Key Constraint khi tạo bảng:
CREATE TABLE
( ColumnName DataType not null [CONSTRAINT ConstraintName] PRIMARY KEY )
Create Table tbl_SinhVien
( MaSV char(10) not null Primary Key,
TenSV Nchar(100) Not Null )
Create Table tbl_MonHoc
( MaMH char(10) not null
Constraint PK_MaMH Primary Key,
TenMH Nchar(100) Not Null )
21
Định nghĩa Primary Key Constraint bảng đã tồn tại:
ALTER TABLE TableName
ADD [CONSTRAINT ConstraintName]
PRIMARY KEY (ColumnName)

VD:
Create Table tbl_Diem
( MaSV char(10) Not null,
MaMH char(10) Not null,
Diem float Check (DiemThi Between 0 and 10 )
)
Alter Table tbl_Diem
Add Constraint PK_MaSV_MaMH
Primary Key (MaSV, MaMH )
22
Định nghĩa Primary Key Constraint bảng đã tồn tại:
ALTER TABLE TableName
ADD [CONSTRAINT ConstraintName]
PRIMARY KEY (ColumnName)

VD:
Create Table tbl_Diem
( MaSV char(10) Not null,
MaMH char(10) Not null,
Diem float Check (DiemThi Between 0 and 10 )
)
Alter Table tbl_Diem
Add Constraint PK_tbl_Diem
Primary Key (MaSV, MaMH )
23
Lưu ý: Không thể xóa một Primary Key Constraint nếu nó được tham chiếu bởi một Foreign key Constraint của một bảng khác, muốn xóa thì phải xóa Foreign key Constraint trước
24
6. Forein key Constraint
Một khóa ngoại (Foreign Key) sự kết hợp của một hoặc nhiều cột được thiết lập và tuân theo một liên kết giữa các dữ liệu trong hai bảng.
Mặc dù mục đích chính của một Constraint Foreign Key là điều khiển dữ liệu có thể được chứa trong bảng khóa ngoại, nhưng nó củng điều khiển các thay đổi đối với bảng khóa chính.
Một Constraint Foreign Key sẽ ngăn chặn tình trạng mồ côi dữ liệu. Constraint Foreign Key sẽ bảo đảm không cho phép bạn xóa dữ liệu trong bản chính chứa Primary Key
25
Định nghĩa Foreign Key Constraint khi tạo Table:
CREATE TABLE Table_Name
( Column_Name DataType [,…] ,
[CONSTRAINT Constraint_Name]
FOREIGN KEY [(column [,…n])]
REFERENCES ref_table [(ref_column[,…n])]
[ON DELETE {CASCADE | NO ACTION} ]
[ON UPDATE {CASCADE | NO ACTION} ]
[NOT FOR REPLICATION]
26
CREATE TABLE PhongBan
(MaPB int Not null,
TenPB varchar (30)
Constraint PK_PB Primary Key (MaPB) )

Create Table NhanVien
( MaNV int,
TenNV Nvarchar (100),
MaPB int not null,
MaCV int not null
Constraint PK_NV Primary Key (MaNV)
Constraint FK_NVien_PBan Foreign Key (MaPB) References PhongBan (MaPB)
)
27
Đ/nghĩa Foreign Key Constraint khi Table đã tồn tại:
ALTER TABLE TableName
[ WITH CHECK / WITH NO CHECK] ADD
[CONSTRAINT ConstraintName]
FOREIGN KEY [(column [,…n])]
REFERENCES ref_table [(ref_column[,…n])]
[ON DELETE { CASCADE | NO ACTION } ]
[ON UPDATE { CASCADE | NO ACTION } ]
[NOT FOR REPLICATION]
Trong đó:
WITH CHECK: SQL Server sẽ kiểm tra dữ liệu hiện tại có vi phạm ràng buộc hay không, nếu có sẽ không tạo Constraint.
WITH NO CHECK: Tạo Constraint mà không cần kiểm tra dữ liệu có có vi phạm ràng buộc hay không.
28
Vd:
Create Table ChucVu
( MaCV int PRIMARY KEY,
TenCV varchar (30),
HSPC Real )

Alter Table NhanVien
Add Constraint FK_NV_CV Foreign Key (MaCV) References ChucVu (MaCV)

Lưu ý:
ON DELETET CASCADE | NO ACTION
ON UPDATE CASCADE | NO ACTION
29
Định nghĩa UNIQUE Constraint khi tạo Table:
CREATE TABLE TableName
( ColumnName datatype [,…]
[CONSTRAINT ConstraintName]
UNIQUE [CLUSTERED | NONCLUSTERED]
{ (Column [ASC | DESC] [,…])}
[ON {filegroup | DEFAULT}])
)
30
Định nghĩa UNIQUE Constraint Table đã tồn tại:
ALTER TABLE TableName
ADD ColumnName Datatype
[CONSTRAINT ConstraintName]
UNIQUE [CLUSTERED | NONCLUSTERED]
[ON {filegroup / DEFAULT}] [,…]

Hoặc:
ALTER TABLE TableName
ADD [CONSTRAINT ConstraintName]
UNIQUE [CLUSTERED | NONCLUSTERED]
{ (Column [ASC | DESC][,…n]) }
[ ON { filegroup | DEFAULT} ]
31
Định nghĩa và hiệu chỉnh các ràng buộc toàn vẹn dữ liệu bằng Enterprise Manager