Các ràng buộc trong SQL Server

Cursor (Bài trước)

Các ràng buộc - Constraint

Các ràng buộc là những quy tắc mà dữ liệu lưu trữ trong một bảng bắt buộc phải đảm bảo nó. SQL Sever có các ràng buộc như:

NOT NULL đảm bảo dữ liệu cột không có giá trị NULL
UNIQUE giá trị cột này là duy nhất (các hàng khác nhau dẫn tới giá trị khác nhau)
DEFAULT giá trị mặc định của cột nếu khi chèn vào không đưa giá trị
CHECK dữ liệu khi chèn vào cần thỏa mãn điều kiện nào đó
PRIMARY KEY là sự kết hợp của NOT NULL và UNIQUE, giá trị này dùng để xác định các hàng khác nhau của bảng (Viết tắt PK, Pk)
FOREIGN KEY là giá trị duy nhất tham khảo từ bảng khác (Viết tắt FK, Fk)
INDEX thiết lập cột được lập chỉ mục nhằm tăng tốc độ truy vấn dữ liệu của bảng

Để tạo ra các ràng buộc khi tạo bảng, hãy thêm cú pháp tạo ràng buộc ràng buộc sau mỗi định nghĩa tạo cột

CREATE TABLE ten_bang (
    cot1 datatype ràng_buộc,
    cot2 datatype ràng_buộc,
    ....
);

Sau đây tìm hiểu một số ràng buộc

Ràng buộc NOT NULL

Ví dụ sau tạo ra bảng Sinhvien, trong đó cột Namsinh thiết lập có ràng buộc NOT NULL

-- Xóa bảng Sinhvien
IF OBJECT_ID('dbo.Sinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Sinhvien;

-- Tạo bảng Sinhvien
CREATE TABLE Sinhvien
(
Ten VARCHAR(255),
Namsinh INT NOT NULL,
);

Thực hiện chèn dòng dữ liệu

INSERT INTO [dbo].[Sinhvien] ([Ten] ,[Namsinh]) VALUES ('TEN1', 2000)
INSERT INTO [dbo].[Sinhvien] ([Ten] ,[Namsinh]) VALUES (NULL, 2000)

SELECT * FROM Sinhvien

Kết quả chèn thành công, tiếp tục chèn dòng mới nhưng thiết lập Namsinh một giá trị NULL

INSERT INTO [dbo].[Sinhvien] ([Ten] ,[Namsinh]) VALUES ('TEN2', NULL)

Kết quả là thông báo lỗi, vì Namsinh không thể gán bằng NULL

Cannot insert the value NULL into column 'Namsinh', table 'dbo.Sinhvien'; column does not allow nulls. INSERT fails.

Ràng buộc UNIQUE

Ràng buộc này nhằm đảm bảo giá trị cột trong bảng là duy nhất. Ví dụ tạo lại bảng Sinhvien thiết lập thêm cột ID là số nguyên với ràng buộc UNIQUE

-- Xóa bảng Sinhvien
IF OBJECT_ID('dbo.Sinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Sinhvien;

-- Tạo bảng Sinhvien
CREATE TABLE Sinhvien
(
    ID INT UNIQUE,
    Ten VARCHAR(255),
    Namsinh INT NOT NULL,
);

Chèn dữ liệu vào bảng

INSERT INTO Sinhvien (ID, Ten, Namsinh) VALUES (1, 'TEN1', 2000)
INSERT INTO Sinhvien (ID, Ten, Namsinh) VALUES (2, 'TEN2', 2000)
INSERT INTO Sinhvien (ID, Ten, Namsinh) VALUES (3, 'TEN3', 2000)

SELECT * FROM Sinhvien

Như vậy đã chèn thành công 3 hàng, cột ID là các giá trị khác nhau. Giờ tiến hành thêm một hàng mới với ID = 3 (trong bảng đang có một dòng với cột ID có giá trị 3)

INSERT INTO Sinhvien (ID, Ten, Namsinh) VALUES (3, 'TEN4', 2000)

Kết quả là thông báo lỗi, vì ID = 3 đã có trong bảng. Như vậy với UNIQUE, mỗi khi chèn dữ liệu SQL Server sẽ kiểm tra giá trị đó trong bảng chưa từng có.

Cannot insert duplicate key in object 'dbo.Sinhvien'. The duplicate key value is (3).

Các ràng buộc - DEFAULT

Ràng buộc này chỉ ra một giá trị mặc định sẽ gán vào cột nếu khi INSERT dữ liệu vào bảng mà giá trị này không được truyền vào. Ví dụ sau cột Ten gán giá trị mặc định là "NO NAME" nếu khi chèn dữ liêu không chỉ ra giá trịn Ten

-- Xóa bảng Sinhvien
IF OBJECT_ID('dbo.Sinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Sinhvien;

-- Tạo bảng Sinhvien
CREATE TABLE Sinhvien
(
	Ten VARCHAR(255) DEFAULT 'NO NAME',
	Namsinh INT,
);

Chèn thử dữ liệu

INSERT INTO Sinhvien (Ten, Namsinh) VALUES ('TEN1', 2000)
INSERT INTO Sinhvien (Namsinh) VALUES (2000) --Giá trị Ten mặc định sử dụng
INSERT INTO Sinhvien (Namsinh) VALUES (1999) --Giá trị Ten mặc định sử dụng

SELECT * FROM Sinhvien

Các ràng buộc - CHECK

Ràng buộc này để kiểm tra dữ liệu chèn vào thỏa mã điều kiện cầm kiểm tra. Bạn có thể thêm ràng buộc này ngay sau định nghĩa cột với từ khóa CHECK tiếp theo là điều kiện logic kiểm tra, ví dụ, kiểm tra Namsinh khi chèn vào phải từ 2000 đến 2005:

-- Xóa bảng Sinhvien
IF OBJECT_ID('dbo.Sinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Sinhvien;

-- Tạo bảng Sinhvien
CREATE TABLE Sinhvien
(
    Ten VARCHAR(255),
    Namsinh INT CHECK (Namsinh >= 2000 AND Namsinh <=2005)
);

Với ràng buộc như vậy, nếu chèn dữ liệu mà Namsinh ngoài khoảng 2000 - 2005 sẽ phát sinh lỗi. Ngoài ra có thể thêm ràng buộc với tên cụ thể mà không phải sau các định nghĩa cột với cú pháp

CONSTRAINT TEN_CONSTRAINT CHECK (Logic)

Ví dụ:

-- Xóa bảng Sinhvien
IF OBJECT_ID('dbo.Sinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Sinhvien;

-- Tạo bảng Sinhvien
CREATE TABLE Sinhvien
(
    Ten VARCHAR(255),
    Namsinh INT,
    CONSTRAINT CHK_CH CHECK (Namsinh > 2000 AND ...)
);

Ràng buộc PRIMARY KEY

Cột thiết lập có ràng buộc PRIMARY KEY thì dữ liệu cột đó sẽ ứng xử như có cả ràng buộc NOT NULL và UNIQUE, một bảng chỉ được phép có một cột thiết lập là PK, cột này dùng để xác định các dòng trong bản. Ví dụ sau thiết lập cột ID của bảng Sinhvien là PK

-- Xóa bảng Sinhvien
IF OBJECT_ID('dbo.Sinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Sinhvien;

-- Tạo bảng Sinhvien
CREATE TABLE Sinhvien
(
    ID INT PRIMARY KEY,
    Ten VARCHAR(255),
    Namsinh INT NOT NULL,
);

Việc chèn dữ liệu, cột ID sẽ tự động kiểm tra đảm bảo cả NOT NULL, UNIQUE

Việc thiết lập một cột là PK thì cũng phổ biến thiết lập cho cột đó tự động phát sinh dữ liệu (tăng) mỗi khi dòng dữ liệu được chèn vào. Để làm điều đó cho thêm vào định nghĩa cột IDENTITY(seed,increment) với seed là giá trị bắt đầu của dòng đầu tiên chèn vào bảng, cứ mỗi dòng thêm vào sau sẽ cộng thêm increment

Ví dụ sau tạo ra cột ID là PK đồng thời là IDENITY, giá trí tăng 1 mỗi khi có dòng mới.

-- Xóa bảng Sinhvien
IF OBJECT_ID('dbo.Sinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Sinhvien;

-- Tạo bảng Sinhvien
CREATE TABLE Sinhvien
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Ten VARCHAR(255),
    Namsinh INT NOT NULL,
);

Thực hiện chèn dữ liệu vào bảng

INSERT INTO Sinhvien (Ten, Namsinh) VALUES ('TEN1', 2000)
INSERT INTO Sinhvien (Ten, Namsinh) VALUES ('TEN2', 2000)

Như vậy mỗi khi chèn dữ liệu, bạn không cần chỉ ra ID, mà ID này sẽ tự động sinh ra cho bạn. Ngoài ra mỗi khi cần lấy giá trị IDENITY cuối cùng được thêm vào sử dụng đến hàm SCOPE_IDENTITY(), Ví dụ SELECT SCOPE_IDENTITY() AS i

Ràng buộc FOREIGN KEY

Ràng buộc FOREIGN KEY (Viết là FK), như tên gọi của nó là khóa ngoài, nó là một cột (trường) tham chiếu từ bảng này sang bảng khác. Nói cách khác nó dùng để liên kết 2 bảng lại với nhau.

Tạo ràng buộc 1 - 1 : One-One Relationship

Ở bảng Sinhvien trên, mỗi sinh viên cần có thêm thông về thẻ sinh viên và các thông tin này lưu ở bảng riêng Thesinhvien, như vậy mỗi sinh viên có một thẻ sinh viên đây chính là mối quan hệ 1 - 1. Ta tạo ra 2 bảng đó như sau:

IF OBJECT_ID('dbo.Thesinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Thesinhvien;
IF OBJECT_ID('dbo.Sinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Sinhvien;

-- Tạo bảng Sinhvien
CREATE TABLE Sinhvien
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Ten VARCHAR(255) DEFAULT 'NO NAME',
    Namsinh INT NOT NULL CHECK (Namsinh >=2000),
);

--Tao bang Thesinhvien
CREATE TABLE Thesinhvien
(
	ID INT IDENTITY PRIMARY KEY,
	Sothe VARCHAR(255),
	FK_SinhvienID INT
		UNIQUE
		FOREIGN KEY REFERENCES Sinhvien(ID)
);

Ở bảng Thesinhvien đã thiết lập cột FK_SinhvienID đã thiết lập là UNIQUE và nó là FK vì thiết lập FOREIGN KEY, giá trị của nó tham khảo tới cột ID của bảng Sinhvien ==> Bảng Thesinhvien phụ thuộc và liên kết với bảng Sinhvien. Thực hiện một số thao tác dữ liệu:

Chèn dữ liệu

INSERT INTO Sinhvien (Ten, Namsinh) VALUES ('TEN1', 2000)
INSERT INTO Sinhvien (Ten, Namsinh) VALUES ('TEN2', 2001)
INSERT INTO Sinhvien (Ten, Namsinh) VALUES ('TEN3', 2002)


INSERT INTO Thesinhvien(Sothe, FK_SinhvienID) VALUES('TSV0001', 1)
INSERT INTO Thesinhvien(Sothe, FK_SinhvienID) VALUES('TSV0002', 2)
INSERT INTO Thesinhvien(Sothe, FK_SinhvienID) VALUES('TSV0003', 3)

SELECT * FROM Thesinhvien
SELECT * FROM Sinhvien

Như vậy, mỗi dòng trong Thesinhvien đã tương ứng một dòng trong Sinhvien, nều chèn dòng vào Thesinhvien mà không chỉ ra FK_Sinhvien bằng với Sinhvien.ID sẽ gây lỗi, hoặc có chỉ ra mà giá trị này có sử dụng cho một dòng khác của Thesinhvien cũng sẽ gây lỗi.

Giờ tiến hành thử xóa một dòng trong Sinhvien:

DELETE FROM Sinhvien WHERE ID = 1

Có thông báo lỗi không xóa được. Nguyên nhân là dòng này (ID) đang được một dòng của Thesinhvien tham chiếu đến. Vấy muốn xóa cần xóa dữ liệu liên quan từ Thesinhvien trước.

DELETE FROM Thesinhvien WHERE FK_SinhvienID = 1
DELETE FROM Sinhvien WHERE ID = 1

Đã xóa thành công.

Trong trường hợp nếu muốn tự động khi xóa một dòng trong Sinhvien, thì dữ liệu liên quan ở bảng phụ thuộc Thesinhvien cũng xóa theo, thì khi tạo FK thêm vào ON DELETE CASCADE

--Tao bang Thesinhvien
CREATE TABLE Thesinhvien
(
	ID INT IDENTITY PRIMARY KEY,
	Sothe VARCHAR(255),
	FK_SinhvienID INT
		UNIQUE
		FOREIGN KEY REFERENCES Sinhvien(ID)
        ON DELETE CASCADE
);

Tạo ràng buộc 1 - M : One-Many Relationship (Một - Nhiều)

Ví dụ mỗi sinh viên có một bảng điểm cá môn học, có thể tạo bảng có tên Diem. Vậy một sinh viên có thể có nhiều dòng dữ liệu trong Diem tương ứng với số môn đã học. Đây chính là mối quan hệ MỘT - NHIỀU. Ta tao FK theo cách như 1 - 1 nhưng bỏ đi từ khóa UNIQUE

-- Xóa bảng Thesinhvien, SinhVien
IF OBJECT_ID('dbo.Thesinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Thesinhvien;
IF OBJECT_ID('dbo.Diem', 'U')
IS NOT NULL  DROP TABLE dbo.Diem;
IF OBJECT_ID('dbo.Sinhvien', 'U')
IS NOT NULL  DROP TABLE dbo.Sinhvien;

-- Tạo bảng Sinhvien
CREATE TABLE Sinhvien
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Ten VARCHAR(255) DEFAULT 'NO NAME',
    Namsinh INT NOT NULL CHECK (Namsinh >=2000),
);

--Tao bang Thesinhvien
CREATE TABLE Diem
(
	ID INT IDENTITY PRIMARY KEY,
	Monhoc VARCHAR(255),
	Diem int,
	FK_SinhvienID INT
		FOREIGN KEY REFERENCES Sinhvien(ID)
		ON DELETE CASCADE --Cho phep xoa theo Sinhvien
);

INSERT INTO Sinhvien (Ten, Namsinh) VALUES ('TEN1', 2000)
INSERT INTO Sinhvien (Ten, Namsinh) VALUES ('TEN2', 2001)
INSERT INTO Sinhvien (Ten, Namsinh) VALUES ('TEN3', 2002)

INSERT INTO Diem (Monhoc, Diem, FK_SinhvienID) VALUES('A',5,2)
INSERT INTO Diem (Monhoc, Diem, FK_SinhvienID) VALUES('B',6,2)
INSERT INTO Diem (Monhoc, Diem, FK_SinhvienID) VALUES('C',7,2)
INSERT INTO Diem (Monhoc, Diem, FK_SinhvienID) VALUES('D',8,2)

SELECT * FROM Diem

Tạo INDEX trong SQL Server

Tạo một chỉ mục cho bảng nào đó nhằm tăng tốc độ truy vấn, để tạo chỉ mục thực hiện theo cú pháp

CREATE UNIQUE INDEX ten_chi_muc ON ten_bang (cot1, cot2, ...)

Nếu cho phép trùng giá trị thì bỏ UNIQUE đi

Để xóa bỏ một INDEX dùng cú pháp

DROP INDEX ten_bang.ten_chi_muc

ĐĂNG KÝ KÊNH, XEM CÁC VIDEO TRÊN XUANTHULAB

Cursor (Bài trước)

(Bài tiếp) Thu gọn Log, Data