SQL CHECK Constraint

SQL CHECK on CREATE TABLE

Ràng buộc CHECK được sử dụng để giới hạn phạm vi giá trị có thể được đặt trong một cột.

Nếu bạn xác định ràng buộc CHECK trên một cột duy nhất, nó chỉ cho phép một số giá trị nhất định cho cột này.

Nếu bạn xác định ràng buộc CHECK trên bảng, nó có thể giới hạn các giá trị trong các cột nhất định dựa trên các giá trị trong các cột khác trong hàng.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

SQL CHECK on ALTER TABLE

Để tạo ràng buộc KIỂM TRA trên cột "Age" khi bảng đã được tạo, hãy sử dụng SQL sau:

ALTER TABLE Persons
ADD CHECK (Age>=18);

Để cho phép đặt tên cho ràng buộc CHECK và để xác định ràng buộc CHECK trên nhiều cột, hãy sử dụng cú pháp SQL sau:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

DROP a CHECK Constraint

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

Và sau đây sẽ là bài viết để chúng ta hiểu rõ hơn :( bài trên không hiểu tại sao ADD CHECK không hoạt động :(

Ràng buộc (Constraint) trong SQL

Constraint là gì?

Constraint là những quy tắc được áp dụng trên các cột dữ liệu, trên bảng. Được sử dụng để kiểm tra tính hợp lệ của dữ liệu vào, đảm bảo tính chính xác, tính toàn vẹn của dữ liệu.

Các loại constraint phổ biến

Loại ràng buộc	Ý nghĩa
NOT NULL	Sử dụng để đảm bảo dữ liệu của cột không được nhận giá trị NULL
DEFAULT	Gán giá trị mặc định trong trường hợp dữ liệu của cột không được nhập vào hay không được xác định.
UNIQUE	Sử dụng để đảm bảo dữ liệu của cột là duy nhất, không trùng lặp giá trị trên cùng 1 cột.
PRIMARY KEY (Khóa chính)	Dùng để thiết lập khóa chính trên bảng, xác định giá trị trên tập các cột làm khóa chính phải là duy nhất, không được trùng lặp. Việc khai báo ràng buộc khóa chính yêu cầu các cột phải NOT NULL.
FOREIGN KEY (Khóa ngoại)	Dùng để thiết lập khóa ngoại trên bảng, tham chiếu đến bảng khác thông qua giá trị của cột được liên kết. Giá trị của cột được liên kết phải là duy nhất trong bảng kia.
CHECK	Bảo đảm tất cả giá trị trong cột thỏa mãn điều kiện nào đó. Đây là hình thức sử dụng phổ biến để kiểm tra tính hợp lệ của dữ liệu (validate data)

Một số lưu ý đối với ràng buộc CHECK:

• Không thể định nghĩa trong VIEW

• Các điều kiện thiết lập phải tham chiếu đến cột trong cùng 1 bảng dùng để khai báo ràng buộc, không thể tham chiếu tới các cột ở bảng khác. Trường hợp muốn tham chiếu đến bảng khác thì có thể dùng Function để trích xuất dữ liệu.

• Không thể sử dụng subquery (truy vấn con) trong định nghĩa điều kiện

• Chúng ta có thể khai báo ràng buộc trong câu lệnh CREATE TABLE (tạo mới bảng) hoặc ALTER TABLE (Sửa đổi bảng)

Ví dụ cụ thể

Cho bài toán quản lý Vay có thế chấp tài sản đơn giản thể hiện qua sơ đồ mức vật lý như sau:

1. Thiết lập ràng buộc NOT NULL trên cột MaKH của bảng KhachHang

• Trường hợp tạo mới table:

CREATE TABLE KhachHang (
	MaKH char(10) NOT NULL,
	HoTen nvarchar(50),
	DiaChi nvarchar(50),
	DienThoai varchar(50),
	NgaySinh date
);

• Trường hợp sửa đổi table:

ALTER TABLE KhachHang MODIFY MaKH char(10) NOT NULL;

2. Thiết lập ràng buộc DEFAULT cột NgayVay lấy giá trị mặc định là ngày hiện tại trong trường hợp ngày vay không được xác định. (Sử dụng function GETDATE() để lấy giá trị ngày hiện tại)

• Trường hợp tạo mới table:

CREATE TABLE Vay (
	MaVay char(10) NOT NULL,
	MaKH char(10) NOT NULL,
	MaTaiSan char(10) NOT NULL,
	MaNV char(10) NOT NULL,
	NgayVay date DEFAULT GETDATE(),
	ThoiHan int,
	LaiSuat float,
	SoTienVay money,
	NgayHetHan date 
);

• Trường hợp sửa đổi table:

ALTER TABLE Vay
ADD CONSTRAINT df_ngay_vay DEFAULT GETDATE() FOR NgayVay;

3. Thiết lập ràng buộc PRIMARY KEY (khóa chính) cho cột MaTaiSan trên table TaiSan

• Trường hợp tạo mới table:

Cách 1: định nghĩa trực tiếp khi khai báo cột, không khai báo constraint

CREATE TABLE TaiSan (
	MaTaiSan char(10) PRIMARY KEY,
	TenTaiSan nvarchar(70) NOT NULL,
	LoaiTaiSan nvarchar(30),
	GiaTri money 
);

Cách 2: Định nghĩa constraint

CREATE TABLE TaiSan (
	MaTaiSan char(10) NOT NULL,
	CONSTRAINT pk_ma_ts PRIMARY KEY (MaTaiSan),
	TenTaiSan nvarchar(70) NOT NULL,
	LoaiTaiSan nvarchar(30),
	GiaTri money 
);

• Trường hợp sửa đổi table:

ALTER TABLE TaiSan
ADD CONSTRAINT pk_mats PRIMARY KEY (MaTaiSan);

4. Thiết lập ràng buộc FOREIGN KEY (khóa ngoại) cho cột MaKH trên table Vay

• Trường hợp tạo mới table:

Cách 1: định nghĩa trực tiếp khi khai báo cột, không khai báo constraint

CREATE TABLE Vay (
	MaVay char(10) NOT NULL,
	MaKH char(10) FOREIGN KEY REFERENCES KhachHang(MaKH),
	MaTaiSan char(10),
	MaNV char(10),
	NgayVay date,
	ThoiHan int,
	LaiSuat float,
	SoTienVay money,
	NgayHetHan date 
);

Cách 2: Định nghĩa constraint

CREATE TABLE Vay ( 
  MaVay char(10) NOT NULL,
  MaKH char(10) NOT NULL,
  MaTaiSan char(10) NOT NULL, 
  MaNV char(10) NOT NULL, 
  NgayVay DATETIME DEFAULT CURRENT_TIMESTAMP, 
  ThoiHan int, 
  LaiSuat float, 
  SoTienVay int, 
  NgayHetHan date 
 )

• Trường hợp sửa đổi table:

ALTER TABLE Vay
ADD CONSTRAINT fk_makh FOREIGN KEY (MaKH) REFERENCES KhachHang(MaKH);

5. Tạo ràng buộc UNIQUE (duy nhất) trên trường DienThoai của Bảng KhachHang

• Trường hợp tạo mới table

Cách 1:

CREATE TABLE KhachHang (
	MaKH char(10) NOT NULL,
	HoTen nvarchar(50) NOT NULL,
	DiaChi nvarchar(50) NOT NULL,
	DienThoai varchar(50) UNIQUE,
	NgaySinh date
);

Cách 2:

CREATE TABLE KhachHang (
	MaKH char(10) NOT NULL,
	HoTen nvarchar(50) NOT NULL,
	DiaChi nvarchar(50) NOT NULL,
	DienThoai varchar(50),
    CONSTRAINT unique_dien_thoai UNIQUE (DienThoai)
	NgaySinh date
);

• Trường hợp sửa đổi table:

ALTER TABLE KhachHang ADD CONSTRAINT unique_dien_thoai UNIQUE (DienThoai);

Lưu ý:

Nên sử dụng Cách 2 trong ở trên mình đưa ra, hay chính là việc chúng ta nên sử dụng kiểu khai báo có đặt tên ràng buộc thông qua khai báo bằng lệnh CONSTRAINT. Vì với cách này bạn sẽ dễ dàng xóa bỏ, sửa đổi, bỏ kích hoạt, kích hoạt vì nó xác định qua tên của ràng buộc

6. Ví dụ một số ràng buộc CHECK (có vẻ hoạt động không đúng :(

• Tạo ràng buộc check trên trường NgayVaoLam > ‘1/1/1995’ của bảng NhanVien.

ALTER TABLE NhanVien 
ADD CONSTRAINT check_ngay_vao_lam CHECK (NgayVaoLam > '1995-1-1');

• Tạo ràng buộc check trên trường NgayHetHan phải lớn hơn NgayVay của bảng Vay

ALTER TABLE Vay
ADD CONSTRAINT check_ngayhethan CHECK (NgayHetHan > NgayVay);

• Tạo ràng buộc kiểm tra trên trường GiaTri>=0 của bảng TaiSan

ALTER TABLE TaiSan
ADD CONSTRAINT check_gia_tri CHECK (GiaTri > 0);

• Tạo ràng buộc kiểm tra trên trường ThoiHan nằm trong khoảng 1 đến 36 tháng của bảng Vay

ALTER TABLE Vay
ADD CONSTRAINT check_thoi_han CHECK (ThoiHan BETWEEN 1 AND 36);