Ví dụ tính toàn vẹn của cơ sở dữ liệu
|
Ràng buộc (Constraint) là các quy tắc được áp dụng trên các cột dữ liệu của một bảng. Chúng được sử dụng để kiểm tra tính hợp lệ của dữ liệu đầu vào, đảm bảo tính chính xác, độ tin cậy và tính toàn vẹn của dữ liệu trong database. Show Các constraint này có thể áp dụng trên một cột hay một table mà có mức độ ảnh hưởng khác nhau. Nếu chỉ áp dụng trên một cột thì cột đó bắt buộc phải đảm bảo thoả mãn các điều kiện được đinh nghĩa trong constraint, trong khi áp dụng ở mức table thì tất cả các cột phải thoả mãn. Các loại ràng buộc phổ biếnDưới đây là một số các ràng buộc phổ biến nhất được sử dụng trong SQL.
Các ràng buộc có thể được chỉ định khi bạn tạo bảng với câu lệnh CREATE TABLE hoặc sau khi tạo bảng với câu lệnh ALTER TABLE. Not NullNếu chúng ta chỉ định một trường của một table phải Not Null. Thì dữ liệu của sẽ đảm bảo không bao giờ trả về null khi chúng ta truy vấn. Hơn nữa, bạn sẽ không được phép để trống giá trị của trường này khi thêm hay cập nhật dữ liệu. Ví dụ, mã SQL bên dưới sẽ tạo một bảng Student với trường ID và NAME Not Null. CREATE TABLE Student ( ID int(6) NOT NULL, NAME varchar(10) NOT NULL, ADDRESS varchar(20) );UNIQUEUNIQUE là ràng buộc trên giá trị duy nhất trên các cột, có nghĩa là các dòng dữ liệu không được có giá trị trùng nhau ở những cột đó đó. Ràng buộc này giống như khóa chính vậy, vì bản chất nó cũng là một khóa. Ví dụ trong bảng SINHVIEN sẽ có MASINHVIEN là khóa chính, và cột CMND sẽ là UNIQUE. Thực ra bạn cũng có thể lấy CMND là khoa chính nhưng như vậy sẽ không hay lắm. Cách này bạn sẽ đặt từ khóa UNIQUE ở cuối column muốn nó có giá trị không trùng lặp. CREATE TABLE hr.persons( person_id INT IDENTITY PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE );Trường hợp này bạn đặt UNIQUE (column_list) ở cuối lệnh Create Table, nếu có nhiều cột thì cách nhau bởi dấu phẩy. CREATE TABLE hr.persons( person_id INT IDENTITY PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255), UNIQUE(email) );PRIMARY KEYPrimary key được dùng để đinh nghĩa các khoá chính trong một bảng. Khóa chính là một trường hoặc kết hợp nhiều trường và được xác định là một bản ghi duy nhất. Không trường nào trong khóa chính được chứa giá trị NULL. Một bảng chỉ có duy nhất một khóa chính. Khóa chính có thể được định nghĩa bằng lệnh CREATE TABLE hoặc lệnh ALTER TABLE. CREATE TABLE Student ( ID int(6) NOT NULL UNIQUE, NAME varchar(10), ADDRESS varchar(20), PRIMARY KEY(ID) );FOREIGN KEYDùng để thiết lập một khoá ngoại cho một bảng, những giá trị này phải đảm bảo tham chiếu đến một khoá chính trong một bảng khác. CREATE TABLE Orders ( O_ID int NOT NULL, ORDER_NO int NOT NULL, C_ID int, PRIMARY KEY (O_ID), FOREIGN KEY (C_ID) REFERENCES Customers(C_ID) )CHECKSử dụng Check Constraint chúng ta có thể chỉ định một điều kiện cho một trường cụ thể. Ví dụ tạo một constraint cho trường AGE bắt buộc Not Null và phải có giá trị trên 18 CREATE TABLE Student ( ID int(6) NOT NULL, NAME varchar(10) NOT NULL, AGE int NOT NULL CHECK (AGE >= 18) );DEFAULTConstraint này được sử dụng để cung cấp giá trị mặc định cho một trường dữ liệu kho nó không được cung cấp tại thời điểm thêm hay cập nhật. Ví dụ tạo constraint đảm bảo giá trị của AGE sẽ luôn là 18 khi nó không được cung cấp. CREATE TABLE Student ( ID int(6) NOT NULL, NAME varchar(10) NOT NULL, AGE int DEFAULT 18 );Xóa bỏ ràng buộc (Drop Constraint)Bất kỳ ràng buộc nào đã định nghĩa đều có thể bị xóa bỏ bằng cách sử dụng lệnh ALTER TABLE với tùy chọn DROP CONSTRAINT. Ví dụ, để xoá constraint khóa chính trong bảng EMPLOYEES, bạn có thể sử dụng lệnh sau đây. ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;Một số cơ sở dữ liệu có thể cung cấp các phím tắt để xoá các ràng buộc nhất một cách ngắn gọn hơn. Ví dụ, để loại bỏ ràng buộc khóa chính cho một bảng trong Oracle, bạn có thể sử dụng lệnh sau đây. Ngoài ra có một số cơ sở dữ liệu cho phép vô hiệu hoá một constraint thay vì xoá chúng vĩnh viễn. Sẽ rất thích hợp khi bạn chỉ muốn ngắt nó đi tạm thời và sau đó lại kích hoạt nó hoạt động trở lại. Nguồn tham khảo https://www.tutorialspoint.com/sql/sql-constraints.htm https://www.geeksforgeeks.org/sql-constraints/
Toàn vẹn dữ liệu (Data Integrity) của một CSDL là cách xây dựng CSDL làm sao để dữ liệu chứa trong CSDL này phải chính xác và đáng tin cậy. Nếu dữ liệu chứa trong CSDL không chính xác ta nói CSSL đó mất tính toàn vẹn (lost data integrity). Trong bài này chúng ta sẽ bàn qua các phương pháp để giữ cho CSDL được toàn vẹn. Nội dung bài họcCác phương pháp toàn vẹn dữ liệuSQL Server dùng một số cách để đảm bảo toàn vẹn dữ liệu. Một số cách như Triggers hay Index sẽ được bàn đến trong các bài sau tuy nhiên trong phạm vi bài này chúng ta cũng nói sơ qua các cách trên.
Các ràng buộcCác ràng buộc là những thuộc tính (property) mà ta áp đặt lên một bảng hay một cột để tránh việc lưu dữ liệu không chính xác vào CSDL (invalid data). Thật ra NOT NULL hay DEFAULT cũng được xem là một dạng ràng buộc nhưng chúng ta không bao gồm hai loại này ở đây mà chỉ trình bày 4 loại ràng buộc là ràng buộc khoá chính (Primary Key Constraint), ràng buộc duy nhất (Unique Constraint), ràng buộc khoá ngoại (Foreign Key Constraint) và ràng buộc kiểm tra (Check Constraint). Primary Key Constraint:Một bảng thường có một hay nhiều cột có giá trị mang tính duy nhất để xác định một hàng bất kỳ trong bảng. Ta thường gọi là Primary Key và được tạo ra khi ta Create hay Alter một bảng với Primary Key Constraint. Một table chỉ có thể có một Primary Key constraint. Có thể có nhiều cột tham gia vào việc tạo nên một Primary Key, các cột này không thể chứa Null và giá trị trong các cột thành viên có thể trùng nhau nhưng giá trị của tất cả các cột tạo nên Primary Key phải mang tính duy nhất. Khi một Primary Key được tạo ra một Unique Index sẽ được tự động tạo ra để duy trì tính duy nhất. Nếu trong bảng đó chưa có Clustered Index thì một Unique + Clustered Index sẽ được tạo ra. Có thể tạo ra Primary Key Constraint như sau: CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 VARCHAR(30) )hoặc CREATE TABLE Table1 (Col1 INT, Col2 VARCHAR(30), CONSTRAINT table_pk PRIMARY KEY (Col1) )Unique ConstraintBạn có thể tạo Unique Constraint để đảm bảo giá trị của một cột nào đó không bị trùng lập. Tuy Unique Constraint và Primary Key Constraint đều đảm bảo tính duy nhất nhưng bạn nên dùng Unique Constraint trong những trường hợp sau:
Cách tạo ra Unique Constraint cũng tương tự như Primary Key Constraint chỉ việc thay chữ Primary Key thành Unique. SQL Server sẽ tự động tạo ra một non-clustered unique index khi ta tạo một Unique Constraint. Foreign Key ConstraintForeign Key là một cột hay một sự kết hợp của nhiều cột được sử dụng để áp đặt mối liên kết data giữa hai table. Foreign key của một table sẽ giữ giá trị của Primary key của một table khác và chúng ta có thể tạo ra nhiều Foreign key trong một table. Foreign key có thể reference (tham chiếu) vào Primary Key hay cột có Unique Constraints. Foreign key có thể chứa Null. Mặc dù mục đích chính của Foreign Key Constraint là để kiểm soát data chứa trong table có Foreign key (tức table con) nhưng thực chất nó cũng kiểm soát luôn cả data trong table chứa Primary key (tức table cha). Ví dụ nếu ta delete data trong table cha thì data trong table con trở nên “mồ côi” (orphan) vì không thể reference ngược về table cha. Do đó Foreign Key constraint sẽ đảm bảo điều đó không xảy ra. Nếu bạn muốn delete data trong table cha thì trước hết bạn phải drop hay disable Foreign key trong table con trước. Có thể tạo ra Foreign Key Constraints như sau: CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 INT REFERENCES Employees(EmployeeID) )hoặc Ðôi khi chúng ta cũng cần Disable Foreign Key Constraint trong trường hợp:
Check ConstraintCheck Constraint dùng để giới hạn hay kiểm soát giá trị được phép chèn vào một cột. Check Constraint giống Foreign Key Constraint ở chỗ nó kiểm soát giá trị đưa vào một cột nhưng khác ở chỗ Foreign Key Constraint dựa trên giá trị ở table cha để cho phép một giá trị được chấp nhận hay không trong khi Check Constraint dựa trên một biểu thức logic (logic expression) để kiểm tra xem một giá trị có hợp lệ không. Ví dụ ta có thể áp đặt một Check Constraint lên cột Lương để chỉ chấp nhận tiền lương từ $15000 đến $100000/năm. Ta có thể tạo ra nhiều Check Constraint trên một cột. Ngoài ra ta có thể tạo một Check Constraint trên nhiều cột bằng cách tạo ra Check Constraint ở mức table (table level). Có thể tạo ra Check Constraint như sau: CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 INT CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000), Col3 VARCHAR(30) )Trong ví dụ này ta giới hạn giá trị chấp nhận được của cột Col2 từ 0 đến 1000. Ví dụ sau sẽ tạo ra một Check Constraint giống như trên nhưng ở table level: CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 INT, Col3 VARCHAR(30), CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000) )Tương tự như Foreign Key Constraint đôi khi ta cũng cần disable Check Constraint trong trường hợp Insert hay Update mà việc kiểm soát tính hợp lệ của data không áp dụng cho data hiện tại. Trường hợp thứ hai là replication. Muốn xem hay tạo ra Constraint trong SQL Management Studio thì làm như sau: Kích lên trên một table nào đó và chọn Design -> Kích chuột phải vào một cột nào đó của bảng rồi chọn “Check Constraints…” Các kỹ thuật truy vấn nâng caoTrong phần này chúng ta sẽ đào sâu một số câu lệnh nâng cao như SELECT, INSERT… Có thể nói hầu như ai cũng biết qua câu lệnh căn bản kiểu như “SELECT * FROM TEN_BANG WHERE…” nhưng có thể có nhiều bạn không biết đến truy vấn nâng cao của nó. Cú pháp đầy đủ của một câu lệnh SELECT rất phức tạp tuy nhiên ở đây chỉ trình bày những nét chính của lệnh này mà thôi: SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]Chúng ta sẽ lần lượt nghiên cứu từng mệnh đề (clause)trong câu lệnh này. Mệnh đề SELECTSau từ khoá (keyword) SELECT ta sẽ có một danh sách các cột mà ta muốn chọn được cách nhau bằng dấu “,”. Có 3 từ khoá cần nhấn mạnh trong phần SELECT.
Ngoài ra trong Select list ta có thể select dưới dạng một biểu thức (expression) như sau: SELECT FirstName + ' ' + LastName AS "Employee Name", IDENTITYCOL AS "Employee ID",HomePhone,Region FROM Northwind.dbo.Employees ORDER BY LastName, FirstName ASCTrong ví dụ trên ta select cột “Employee Name” là kết quả ghép lại của cột FirstName và LastName được cách nhau bằng một khoảng trắng. Một giá trị thuộc loại identity để làm cột “Employee ID”. Kết quả sẽ được sắp theo thứ tự từ nhỏ tới lớn (ASC) (còn DESC là từ lớn tới nhỏ) trong đó cột LastName được sắp trước rồi mới tới cột FirstName. Mệnh đề INTOINTO Clause cho phép ta select data từ một hay nhiều table sau đó kết quả sẽ được insert vào một table mới. Table này được tạo ra do kết quả của câu lệnh SELECT INTO. Ví dụ: SELECT FirstName, LastName INTO EmployeeNames FROM EmployersCâu lệnh tên sẽ tạo ra một table mới có tên là EmployeeNames với 2 cột là FirstName và LastName sau đó kết quả select được từ table Employers sẽ được insert vào table mới này. Nếu table EmployeeNames tồn tại SQL Server sẽ báo lỗi. Câu lệnh này thường hay được sử dụng để select một lượng data lớn từ nhiều table khác nhau vào một table mới (thường dùng cho mục đích tạm thời (temporary table)) mà khỏi phải thực thi câu lệnh Insert nhiều lần. Một cách khác cũng chọn dữ liệu từ một hay nhiều table và insert vào một bảng khác là dùng “Insert Into…Select…”. Nhưng câu lệnh này không tạo ra một table mới. Nghĩa là bảng đó phải tồn tại trước. Ví dụ: INSERT INTO EmployeeNames SELECT FirstName, LastName FROM EmployersChú ý là không có chữ “Value” trong câu Insert này. Mệnh đề GROUP BY và HAVINGGROUP BY dùng để nhóm hay tổng hợp giá trị (aggregate values) cho từng hàng trong kết quả select được. Chỉ có một hàng cho từng giá trị riêng biệt (distinct) của từng cột. Các cột được select đều phải nằm trong mệnh đề GROUP BY. Hãy xem ví dụ phức tạp sau: SELECT OrdD1.OrderID AS OrderID, SUM(OrdD1.Quantity) AS "Units Sold", SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue FROM [Order Details] AS OrdD1 WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID FROM [Order Details] AS OrdD2 WHERE OrdD2.UnitPrice > $100) GROUP BY OrdD1.OrderID HAVING SUM(OrdD1.Quantity) > 100Trong ví dụ trên đầu tiên ta chọn những order riêng biệt (distinct) từ bảng Order Details với UnitPrice > 100. Sau đó tiếp tục select OrderID, “Units Sold”, Revenue từ kết quả trên trong đó “Units Sold” và Revenue là những aggregate columns (cho giá trị tổng một cột của những hàng có cùng OrderID). HAVING Clause đóng vai trò như một filter dùng để lọc lại các giá trị cần select mà thôi. HAVING Clause thường đi chung với GROUP BY mặc dù có thể xuất hiện riêng lẻ. UNIONTừ khoá Uninon có nhiệm vụ ghép nối kết quả của 2 hay nhiều truy vấn lại thành một kết quả. Ví dụ: Giả sử có table1(ColumnA varchar(10), ColumnB int) và table2(ColumnC varchar(10), ColumnD int). Ta muốn select data từ table1 và ghép với data từ table2 để tạo thành một kết quả duy nhất ta làm như sau: SELECT * FROM Table1 UNION ALL SELECT * FROM Table2Nếu không có từ khoá ALL thì những hàng giống nhau từ 2 table sẽ chỉ xuất hiện một lần trong kết quả. Còn khi dùng ALL thì các hàng trong 2 table đều có trong kết quả bất chấp việc lập lại. Khi dùng Union phải chú ý hai việc: số cột select ở 2 truy vấn phải bằng nhau và data type của các cột tương ứng phải tương thích (compatible). Sử dụng JOINTrong phần này chúng ta sẽ tìm hiểu về các loại Join trong SQL Server. Bằng cách sử dụng JOIN bạn có thể select data từ nhiều table dựa trên mối quan hệ logic giữa các table (logical relationships). Có thể tóm tắt các loại Join thông dụng bằng các hình sau:  Thực hiện JOIN trong SQL Thứ tự từ trái sang phải: Inner Join, Left Outer Join, Right Outer Join, Full Outer Join Inner JoinsDùng Inner Join để select data từ 2 hay nhiều tables trong đó giá trị của các cột được join phải xuất hiện ở cả 2 tables tức là phần gạch chéo trên hình. Ví dụ: SELECT t.Title, p.Pub_name FROM Publishers AS p INNER JOIN Titles AS t ON p.Pub_id = t.Pub_id ORDER BY Title ASCLeft Outer JoinsDùng Left Outer Join để select data từ 2 hay nhiều tables trong đó tất cả cột bên table thứ nhất và không tồn tại bên table thứ hai sẽ được select cộng với các giá trị của các cột được inner join. Số cột select được sẽ bằng với số cột của table thứ nhất. Tức là phần tô màu đỏ trên hình. Ví dụ: USE Pubs SELECT a.Au_fname, a.Au_lname, p.Pub_name FROM Authors a LEFT OUTER JOIN Publishers p ON a.City = p.City ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASCRight Outer JoinsDùng Right Outer Join để select data từ 2 hay nhiều tables trong đó tất cả cột bên table thứ hai và không tồn tại bên table thứ nhất sẽ được select cộng với các giá trị của các cột được inner join. Số cột select được sẽ bằng với số cột của table thứ hai. Tức là phần tô màu đỏ trên hình. Full Outer JoinsDùng Full Outer Join để select dữ liệu từ 2 hay nhiều bảng trong đó tất cả cột bên bảng thứ nhất và thứ hai đều được chọn các giá trị bên hai bảng bằng nhau thì chỉ lấy một lần. Tức là phần tô màu đỏ trên hình. Cross JoinsDùng Cross Join ghép dữ liệu từ hai bảng trong đó số hàng thu được bằng với số hàng của bảng thứ nhất nhân với số hàng của bảng thứ hai. Ðể ý là trong câu lệnh này không có từ khoá “On“. Muốn hiểu rõ hơn về các loại JOIN, bạn cho chạy thử trên SQL Server và làm phần bài tập số 1. Kết luậnTóm lại trong bài này chúng ta đã tìm hiểu các kỹ thuật toàn vẹn dữ liệu trong SQL Server bằng cách dùng các loại Constraint. Ngoài ra chúng ta cũng biết qua về một số kỹ thuật truy vấn nâng cao. Sau bài học này các bạn cần làm bài tập số 1 để hệ thống hóa lại kiến thức đã học từ bài 1 đến bài 5 trước khi bạn học tiếp bài 6. Khi làm bài tập nhớ phải làm theo thứ tự và tuân thủ theo các yêu cầu của bài tập đặt ra. Không nên bỏ qua bước nào. Tham khảo
|
