Các câu lệnh SQL đã đề cập đến được sử dụng nhằm thực hiện các thao tác bổ sung, cập nhật, loại bỏ và xem dữ liệu. Nhóm các câu lệnh này được gọi là ngôn ngữ thao tác dữ liệu (DML). Trong chuơng này, chúng ta sẽ tìm hiểu nhóm các câu lệnh được sử dụng để định nghĩa và quản lý các đối tượng CSDL như bảng, khung nhìn, chỉ mục,... và được gọi là ngôn ngữ định nghĩa dữ liệu (DLL).
Về cơ bản,ngôn ngữ định nghĩa dữ liệubao gồm các lệnh:
CREATE: định nghĩa và tạo mới đối tượng CSDL.
ALTER:thay đổi định nghĩa của đối tượng CSDL.
DROP: Xoá đối tượng CSDL đã có.
Sau khi có khái niệm về cách thức tổ chức các tập tin vật lý để lưu trữ dữ liệu trong Microsoft SQL Server, chúng ta sẽ tự tạo một cơ sở dữ liệu cho riêng mình nhằm lưu trữ các dữ liệu riêng biệt và đưa vào khai thác các dữ liệu đó. Cách dễ nhất để các bạn tạo ra một cơ sở dữ liệu là sử dụng tiện ích Enterprise Manager. Chỉ những người với vai trò là quản trị hệ thống (sysadmin) thì mới có thể tạo lập cơ sở dữ liệu. Do đó các bạn có thể đăng nhập vào với tên tài khoản người dùng là sa để thực hiện việc tạo cơ sở dữ liệu mới cho ứng dụng của mình. Trước khi giới thiệu từng bước tạo lập cơ sở dữ liệu, phần kế tiếp mà chúng tôi muốn trình bày là các thuộc tính của một cơ sở dữ liệu trong Microsoft SQL Server. Các thuộc tính nhằm giúp các bạn hiểu rõ thêm về bên trong cơ sở dữ liệu của Microsoft SQL Server, chúng gồm có:
Tên cơ sở dữ liệu(database name) : là duy nhất trong một Microsoft SQL Server, độ dài tối đa là 123 ký tự. Theo chúng tôi các bạn nên đặt tên cơ sở dữ liệu gợi nhớ. Thí dụ: QLBanhang (Quản lý bán hàng), QLHocsinh (Quản lý học sinh)...
Vị trí tập tin(File location) : là tên và đường dẫn vật lý của các loại tập tin dữ liệu dùng để lưu trữ cơ sở dữ liệu của Microsoft SQL Server. Thông thường các tập tin này sẽ được lưu tại thư mục C:\MSSQL\DATA.
Tên tập tin(File name) :là tên logic của mỗi loại tập tin dữ liệu tương ứng mà hệ thống Microsoft SQL Server dùng để quản lý bên trong. Tương ứng mỗi loại tập tin dữ liệu sẽ có một tên tập tin riêng biệt.
Kích thước ban đầu(Initial size) : là kích thước khởi tạo của tập tin dữ liệu khi cơ sở dữ liệu mới được tạo lập. Đơn vị tính là MegaByte (MB). Thông thường kích thước ban đầu của một cơ sở dữ liệu mới tối thiểu phải bằng với kích thước của cơ sở dữ liệu Model, bởi vì Microsoft SQL Server sẽ lấy cơ sở dữ liệu Model làm khuôn dạng mẫu khi hình thành một cơ sở dữ liệu mới.
Việc tăng trưởng kích thước tập tin dữ liệu(File growth) : là các qui định cho việc tăng trưởng tự động kích thước tập tin dữ liệu, bởi vì các dữ liệu sẽ được lưu trữ ngày càng nhiều hơn so với kích thước ban đầu khi tạo lập. Việc tăng trưởng sẽ tự động làm tăng kích thước tập tin dữ liệu theo từng MB hoặc theo tỷ lệ phần trăm (by percent) của kích thước hiện hành khi các dữ liệu bên trong Microsoft SQL Server lưu trữ gần đầy so với kích thước tập tin vật lý hiện thời. Mặc định kích thước tập tin dữ liệu sẽ được tăng tự động 10% khi dữ liệu lưu trữ gần đầy.
Kích thước tối đa tập tin dữ liệu(Maximum file size) : là việc qui định sự tăng trưởng tự động kích thước của các tập tin dữ liệu nhưng có giới hạn (restrict file growth) đến MB nào đó hoặc là không có giới hạn (un-restrict file growth). Trong trường hợp nếu các bạn chọn có giới hạn kích thước của tập tin dữ liệu thì chúng ta phải biết tự thêm vào các tập tin dữ liệu mới khi dữ liệu lưu trữ đã bằng với kích thước tối đa của tập tin dữ liệu. Các tập tin dữ liệu mới này chính là loại tập tin thứ yếu (Secondary data file) và chúng ta có thể lưu trữ các tập tin vật lý này tại các đĩa cứng khác có bên trong Microsoft SQL Server. Đây cũng là một trong nét đặc trưng của mô hình cơ sở dữ liệu phân tán (distributed database). Đối với các CSDL thực tế, việc xác định các tham số về kích thước ban đầu rất quan trọng vì nhiều lý do. Để đảm bảo có đủ không gian lưu trữ dữ liệu, bạn cần dành trước cho CSDL phòng khi những ứng hay CSDL khác sử dụng hết đĩa cứng. CSDL có kích thước nhỏ cũng sẽ ảnh hưởng tới tốc độ do SQL Server cần phải thực hiện nhiều lần thao tác mở rộng kích thước tập tin CSDL khi có dữ liệu thêm mới. Ngoài ra, đa số các dữ liệu trong CSDL thực tế theo thời gian không thể xoá bỏ mà cần phải lưu trữ (backup) lại trước. Việc lưu trữ và phục hồi (restore) dữ liệu cũng ảnh hưởng bởi kích thước các tập tin do chúng phải đủ nhỏ để lưu trên các đĩa CD- ROM hay băng từ. Các bước mà chúng tôi mô tả bên dưới sẽ giúp các bạn tạo ra một cơ sở dữ liệu mới bằng tiện ích Enterprise Manager.
Bước1: Khởi động ứng dụng, chọn một (1) Microsoft SQL Server đã được đăng ký quản trị trước đó. Chọn chức năng New Database... trong thực đơn tắt sau khi nhấn chuột phải trên đối tượng Database
Bước2:Trong màn hình các thuộc tính của cơ sở dữ liệu (Database Properties) tại trang General gõ vào tên cơ sở dữ liệu muốn tạo mới.
Bước3:Trong màn hình các thuộc tính của cơ sở dữ liệu (Database Properties) tại trang Data Files, chỉ định kích thước ban đầu khi khởi tạo của tập tin dữ liệu chính, kế tiếp thay đổi các thuộc tính khác (nếu cần). Chuyển sang trang Transaction Log để thay đổi các thuộc tính của tập tin lưu vết theo cách tương tự.
Tùy thuộc vào kích thước của cơ sở dữ liệu mà thời gian thực hiện tạo cơ sở dữ liệu sẽ nhanh hoặc lâu. Ngoài ra chúng ta còn có thể tạo mới một cơ sở dữ liệu bằng câu lệnh CREATE DATABASE được thực hiện trong tiện ích Query Analyzer. Các thành phần trong câu lệnh này hoàn toàn giống với các thuộc tính của cơ sở dữ liệu mà chúng tôi đã giới thiệu trong phần trên.
Để tạo ra một cơ sở dữ liệu có tên QLBanHang với kích thước ban đầu lúc khởi tạo của tập tin dữ liệu chính là 50MB, tự động tăng kích thước lên 10% khi dữ liệu bị đầy, kích thước tăng trưởng tập tin dữ liệu tối đa không quá 200MB. Và tập tin lưu vết với kích thước ban đầu lúc khởi tạo là 10MB, tự động tăng kích thước tập tin lên 5MB khi dữ liệu bị đầy, kích thước tăng trưởng tập tin không giới hạn. Các bạn sẽ thực hiện câu lệnh CREATE DATABASE như sau:
Một cơ sở dữ liệu sau khi tạo xong sau một thời gian dài mà các bạn không còn khai thác dữ liệu bên trong đó thì các bạn có thể hủy bỏ để làm cho dung lượng đĩa trống được tăng lên. Tuy nhiên phải chắc rằng các thông tin dữ liệu trong cơ sở dữ liệu mà các bạn dự định xóa sẽ không còn hữu ích về sau nữa. Bởi vì chúng ta không thể khôi phục khi đã xóa. Để hủy bỏ cơ sở dữ liệu trong Microsoft SQL Server chúng ta có nhiều cách thực hiện: sử dụng câu lệnh DROP DATABASE, nhấn phím Delete hoặc nhấn chuột trên biểu tượng Delete và xác định đồng ý hủy bỏ cơ sở dữ liệu đã chọn trong tiện ích Enterprise Manager. Để hủy bỏ cơ sở dữ liệu QLBanHang, thực hiện câu lệnh DROP DATABSE như sau: DROP DATABASE QLBanHang
Hoặc nhấn phím Delete trên tên của cơ sở dữ liệu này trong tiện ích Enterprise Manager và xác nhận là đồng ý (chọn Yes) để hủy bỏ cơ sở dữ liệu QLBanHang. Hệ thống Microsoft SQL Server không cho người sử dụng có thể hủy bỏ các cơ sở dữ liệu hệ thống như là: Master, Model, Tempdb bởi vì các cơ sở dữ liệu luôn được hệ thống Microsoft SQL Server sử dụng. Ngoài ra để hủy bỏ một cơ sở dữ liệu thành công thì phải đảm bảo không còn người sử dụng nào đang truy cập vào cơ sở dữ liệu đó. Trong trường hợp khi thực hiện hủy bỏ cơ sở dữ liệu đang còn người sử dụng truy cập thì hệ thống sẽ hiển thị thông báo bên dưới.
TuyệtđốikhôngxóacơsởdữliệubằngcáchsửdụngWindowsExplorerhoặcWindows Commanderđểhủybỏcácloại tậptindữliệutrongthưmụcC:\MSSQL\DATA\vìlàmnhưthế sẽ ảnh hưởngtrực tiếpđến hệthống cơ sở dữ liệuMicrosoft SQLServer.
Bảng dữ liệu là cấu trúc có vai trò quan trọng nhất trong cơ sở dữ liệu quan hệ. Toàn bộ dữ liệu của cơ sở dữ liệu được tổ chức trong các bảng, những bảng này có thể là những bảng hệ thống được tạo ra khi tạo lập cơ sở dữ liệu, và cũng có thể là những bảng do người sử dụng định nghĩa.
Trong các bảng, dữ liệu được chức dưới dạng các dòng và cột. Mỗi một dòng là một bản ghi duy nhất trong bảng và mỗi một cột là một trường. Các bảng trong cơ sở dữ liệu được sử dụng để biểu diễn thông tin, lưu giữ dữ liệu về các đối tượng trong thế giới thực và/hoặc mối quan hệ giữa các đối tượng. Bảng trong hình 3.1 bao gồm 10 bản ghi và 4 trường là MAKHOA, TENKHOA, DIENTHOAI và TRUONGKHOA.
Câu lệnh CREATE TABLE được sử dụng để định nghĩa một bảng dữ liệu mới trong cơ sở dữ liệu. Khi định nghĩa một bảng dữ liệu mới, ta cần phải xác định được các yêu cầu sau đây:
Bảng mới được tạo ra sử dụng với mục đích gì và có vai trò như thế nào trong cơ sở dữ liệu.
Cấu trúc của bảng bao gồm những trường (cột) nào, mỗi một trường có ý nghĩa như thế nào trong việc biểu diễn dữ liệu, kiểu dữ liệu của mỗi trường là gì và trường đó có cho phép nhận giá trị NULL hay không.
Những trường nào sẽ tham gia vào khóa chính của bảng. Bảng có quan hệ với những bảng khác hay không và nếu có thì quan hệ như thế nào.
Trên các trường của bảng có tồn tại những ràng buộc về khuôn dạng, điều kiện hợp lệ của dữ liệu hay không; nếu có thì sử dụng ở đâu và như thế nào.
Tạo cấu trúc bảng dữ liệu bằngSQL Management System
Sau khi xem xét và hiểu được các thuộc tính liên quan đến cấu trúc của bảng, trong phần này chúng tôi sẽ hướng dẫn các bạn các cách để tạo cấu trúc bảng dữ liệu mới. Để tạo cấu trúc bảng chúng tôi hướng dẫn các bạn hai (2) cách thực hiện. Đầu tiên là tạo cấu trúc bảng bằng tiện ích Enterprise Manager. Kể từ bây giờ chúng tôi xem như các bạn đã đăng ký quản trị một Microsoft SQL Server và bên trong Microsoft SQL Server này, cơ sở dữ liệu quản lý bán hàng (QLBanHang) đã được tạo lập. Các bảng dữ liệu và những đối tượng khác ở các phần trình bày kế tiếp sẽ được tạo ra bên trong cơ sở dữ liệu QLBanHang này. Các bước thực hiện việc tạo bảng dữ liệu trong Enterprise Manager như sau:
Bước 1:Trong ứng dụng , mở rộng cơ sở dữ liệu để thấy các đối tượng bên trong. Nhấn chuột phải trên đối tượng Tables, chọn chức năng New Table... trong thực đơn tắt.
Bước 2: Trong màn hình thiết kế cấu trúc bảng (design table), lần lượt gõ vào tên các cột bên trong bảng, chọn lựa các kiểu dữ liệu tương ứng thích hợp và chỉ định các thuộc tính cần thiết cho các cột bên trong bảng.
Bước3:Định nghĩa khóa chính cho bảng và lưu lại cấu trúc bảng vừa định nghĩa. Đóng màn hình thiết kế cấu trúc bảng lại để kết thúc quá trình tạo cấu trúc bảng bằng tiện ích SQL Studio.
Tạo cấu trúc bảng bằng T-SQL
Câu lệnh CREATE TABLE có cú pháp như sau:
tên_cột thuộc_tính_cột các_ràng_buộc
,tên_cột_n thuộc_tính_cột_n các_ràng_buộc_cột_n]
[,các_ràng_buộc_trên_bảng]
Tên_bảng Tên của bảng cần tạo. Tên phải tuân theo qui tắc định danh và không được vượt quá 128 ký tự.
tên_cột Là tên của cột (trường) cần định nghĩa, tên cột phải tuân theo qui tắc định danh và không được trùng nhau trong mỗi một bảng. Mỗi một bảng phải có ít nhất một cột. Nếu bảng có nhiều cột thì định nghĩa của các cột (tên cột, thuộc tính và các ràng buộc) phải phân cách nhau bởi dấu phẩy.
Thuộc_tính_cột Mỗi một cột trong một bảng ngoài tên cột còn có các thuộc tính bao gồm:
·Kiểu dữ liệu của cột. Đây là thuộc tính bắt buộc phải có đối với mỗi cột.
·Giá trị mặc định của cột: là giá trị được tự động gán cho cột nếu như người sử d ụng không nhập dữ liệu cho cột một cách tường minh. Mỗi một cột chỉ có thể có nhiều nhất một giá trị mặc định.
·Cột có tính chất IDENTITY hay không? tức là giá trị của cột có được tự động tăng mỗi khi có bản ghi mới được bổ sung hay không. Tính chất này chỉ có thể sử dụng đối vớicác trường kiểu số.
·Cột có chấp nhận giá trị NULL hay không
Đây là phần ví dụ , xác định khoảng của ví dụ đó rồi chọn style
Khai báo dưới đây định nghĩa cột STT có kiểu dữ liệu là int và cột có tính chất IDENTITY:
hay định nghĩa cột NGAY có kiểu datetime và không cho phép chấp nhận giá trịNULL:
và định nghĩa cộtSOLUONGkiểuintvà có giá trị mặc định là0:
Các_ràng_buộc: Các ràng buộc được sử dụng trên mỗi cột hoặc trên bảng nhằm các mục đích sau:
Quy định khuôn dạng hay giá trị dữ liệu được cho phép trên cột (chẳng hạn qui định tuổi của một học sinh phải lớn hơn 6 và nhỏ hơn 20, số điện thoại phải là một chuỗi bao gồm 6 chữ số,...). Những ràng buộc kiểu này được gọi là ràng buộc CHECK
Đảm bảo tính toàn vẹn dữ liệu trong một bảng và toàn vẹn tham chiếu giữa các bảng trong cơ sở dữ liệu. Những loại ràng buộc này nhằm đảm bảo tính đùng của dữ liệu như: số chứng minh nhân dân của mỗi một người phải duy nhất, nếu sinh viên học một lớp nào đó thì lớp đó phải tồn tại,... Liên quan đến những loại ràng buộc này bao gồm các ràng buộc PRIMARY KEY (khoá chính), UNIQUE (khóa dự tuyển) và FOREIGN KEY (khoá ngoài)
Các loại ràng buộc này sẽ được trình bày chi tiết hơn ở phần sau.
Câu lệnh dưới đây định nghĩa bảng NHANVIEN với các trường MANV (mã nhân viên), HOTEN (họ và tên), NGAYSINH (ngày sinh của nhân viên), DIENTHOAI (điện thoại) và HSLUONG (hệ số lương)
manv NVARCHAR(10) NOT NULL,
hoten NVARCHAR(50) NOT NULL,
dienthoai NVARCHAR(10) NULL,
hsluong DECIMAL(3,2) DEFAULT (1.92)
Trong câu lệnh trên, trường MANV và HOTEN của bảng NHANVIEN không được NULL (tức là bắt buộc phải có dữ liệu), trường NGAYSINH và DIENTHOAI sẽ nhận giá trị NULL nếu ta không nhập dữ liệu cho chúng còn trường HSLUONG sẽ nhận giá trị mặc định là 1.92 nếu không được nhập dữ liệu.
Nếu ta thực hiện các câu lệnh dưới đây sau khi thực hiện câu lệnh trên để bổ sung dữ liệu cho bảng NHANVIEN
INSERT INTO nhanvien VALUES('NV01','Le Van A','2/4/75','886963',2.14)
INSERT INTO nhanvien(manv,hoten)VALUES('NV02','Mai Thi B')
INSERT INTO nhanvien(manv,hoten,dienthoai) VALUES('NV03','Tran Thi C','849290')
Ta sẽ có được dữ liệu trong bảng NHANVIEN như sau:
Ràng buộc CHECK được sử dụng nhằm chỉ định điều kiện hợp lệ đối với dữ liệu. Mỗi khi có sự thay đổi dữ liệu trên bảng (INSERT, UPDATE), những ràng buộc này sẽ được sử dụng nhằm kiểm tra xem dữ liệu mới có hợp lệ hay không.
Ràng buộc CHECK được khai báo theo cú pháp như sau:
[CONTRAINT tên_ràng_buộc] CHECK (điều kiện)
Trong đó, điều_kiện là một biểu thức logic tác động lên cột nhằm qui định giá trị hoặc khuôn dạng dữ liệu được cho phép. Trên mỗi một bảng cũng như trên mỗi một cột có thể có nhiều ràng buộc CHECK.
Câu lệnh dưới đây tạo bảng DIEMTOTNGHIEP trong đó qui định giá trị của cột DIEMVAN và DIEMTOAN phải lớn hơn hoặc bằng 0 và nhỏ hơn hoặc bằng 10
CREATE TABLE diemtotnghiep
Hoten NVARCHAR(30) NOT NULL
CHECK(diemvan>=0 AND diemvan<=10), diemtoan DECIMAL(4,2)
CHECK(diemtoan>=0 AND diemtoan<=10),
Như vậy, với định nghĩa như trên của bảng DIEMTOTNGHIEP, các câu lệnh dưới đây là hợp lệ:
CREATE TABLE diemtotnghiep
INSERT INTO diemtotnghiep(hoten,diemvan,diemtoan) VALUES('Le Thanh Hoang',9.5,2.5)
INSERT INTO diemtotnghiep(hoten,diemvan) VALUES('Hoang Thi Mai',2.5)
còn câu lệnh dưới đây là không hợp lệ:
INSERT INTO diemtotnghiep(hoten,diemvan,diemtoan) VALUES('Tran Van Hanh',6,10.5)
do cột DIEMTOAN nhận giá trị 10.5 không thoả mãn điều kiện của ràng buộc
Trong ví dụ trên, các ràng buôc được chỉ định ở phần khai báo của mỗi cột. Thay vì chỉ định ràng buộc trên mỗi cột, ta có thể chỉ định các ràng buộc ở mức bảng bằng cách khai báo các ràng buộc sau khi đã khai báo xong các cột trong bảng.
malop NVARCHAR(10) NOT NULL ,
tenlop NVARCHAR(30) NOT NULL ,
hedaotao NVARCHAR(25) NULL
CONSTRAINT chk_lop_hedaotao
CHECK (hedaotao IN ('chính quy','tại chức')),
CONSTRAINT chk_lop_namnhaphoc
CHECK (namnhaphoc<=YEAR(GETDATE())),
có thể được viết lại như sau:
malop NVARCHAR(10) NOT NULL ,
tenlop NVARCHAR(30) NOT NULL ,
hedaotao NVARCHAR(25) NULL,
CHECK (namnhaphoc<=YEAR(GETDATE()) AND hedaotao IN ('chính quy','tại chức'))
Ràng buộc PRIMARY KEY được sử dụng để định nghĩa khoá chính của bảng. Khoá chính của một bảng là một hoặc một tập nhiều cột mà giá trị của chúng là duy nhất trong bảng. Hay nói cách khác, giá trị của khoá chính sẽ giúp cho ta xác định được duy nhất một dòng (bản ghi) trong bảng dữ liệu. Mỗi một bảng chỉ có thể có duy nhất một khoá chính và bản thân khoá chính không chấp nhận giá trị NULL. Ràng buộc PRIMARY KEY là cơ sở cho việc đảm bảo tính toàn vẹn thực thể cũng như toàn vẹn tham chiếu.
Để khai báo một ràng buộc PRIMARY KEY, ta sử dụng cú pháp như sau:
[CONSTRAINT tên_ràng_buộc] PRIMARY KEY [(danh_sách_cột)]
Nếu khoá chính của bảng chỉ bao gồm đúng một cột và ràng buộc PRIMARY KEY được chỉ định ở mức cột, ta không cần thiết phải chỉ định danh sách cột sau từ khoá PRIMARY KEY. Tuy nhiên, nếu việc khai báo khoá chính được tiến hành ở mức bảng (sử dụng khi số lượng các cột tham gia vào khoá là từ hai trở lên) thì bắt buộc phải chỉ định danh sách cột ngay sau từ khóa PRIMARY KEY và tên các cột được phân cách nhau bởi dấu phẩy.
Câu lệnh dưới đây định nghĩa bảng SINHVIEN với khoá chính là MASV
CONSTRAINT pk_sinhvien_masv PRIMARY KEY, hodem NVARCHAR(25) NOT NULL ,
ten NVARCHAR(10) NOT NULL , ngaysinh DATETIME,
noisinh NVARCHAR(255), malop NVARCHAR(10)
Với bảng vừa được tạo bởi câu lệnh ở trên, nếu ta thực hiện câu lệnh:
INSERT INTO sinhvien(masv,hodem,ten,gioitinh,malop) VALUES('0261010001','Lê Hoàng Phương','Anh',0,'C26101')
một bản ghi mới sẽ được bổ sung vào bảng này. Nhưng nếu ta thực hiện tiếp câu lệnh:
INSERT INTO sinhvien(masv,hodem,ten,gioitinh,malop) VALUES('0261010001','Lê Huy','Đan',1,'C26101')
thì câu lệnh này sẽ bị lỗi do trùng giá trị khoá với bản ghi đã có.
Câu lệnh dưới đây tạo bảng DIEMTHI với khoá chính là tập bao gồm hai cột MAMONHOC và MASV
Diemlan1NUMERIC(4, 2), Diemlan2NUMERIC(4, 2),
CONSTRAINT pk_diemthi PRIMARY KEY(mamonhoc,masv)
Mỗi một bảng chỉ có thể có nhiều nhất một ràng buộc PRIMARY KEY.
Một khoá chính có thể bao gồm nhiều cột nhưng không vượt quá 16 cột.
Trên một bảng chỉ có thể có nhiều nhất một khóa chính nhưng có thể có nhiều cột hoặc tập các cột có tính chất như khoá chính, tức là giá trị của chúng là duy nhất trong bảng. Tập một hoặc nhiều cột có giá trị duy nhất và không được chọn làm khoá chính được gọi là khoá phụ (khoá dự tuyển) của bảng. Như vậy, một bảng chỉ có nhiều nhất một khoá chính nhưng có thể có nhiều khoá phụ.
Ràng buộc UNIQUE được sử dụng trong câu lệnh CREATE TABLE để định nghĩa khoá phụ cho bảng và được khai báo theo cú pháp sau đây:
[CONSTRAINT tên_ràng_buộc] UNIQUE [(danh_sách_cột)]
Giả sử ta cần định nghĩa bảng LOP với khoá chính là cột MALOP nhưng đồng thời lại không cho phép các lớp khác nhau được trùng tên lớp với nhau, ta sử dụng câu lệnh như sau:
hedaotao NVARCHAR(10) NULL
CONSTRAINT pk_lop PRIMARY KEY (malop),
CONSTRAINT unique_lop_tenlop UNIQUE(tenlop)
Các bảng trong một cơ sở dữ liệu có mối quan hệ với nhau. Những mối quan hệ này biểu diễn cho sự quan hệ giữa các đối tượng trong thế giới thực. Về mặt dữ liệu, những mối quan hệ được đảm bảo thông qua việc đòi hỏi sự có mặt của một giá trị dữ liệu trong bảng này phải phụ thuộc vào sự tồn tại của giá trị dữ liệu đó ở trong một bảng khác.
Ràng buộc FOREIGN KEY được sử dụng trong định nghĩa bảng dữ liệu nhằm tạo nên mối quan hệ giữa các bảng trong một cơ sở dữ liệu. Một hay một tập các cột trong một bảng được gọi là khoá ngoại, tức là có ràng buộc FOREIGN KEY, nếu giá trị của nó được xác định từ khoá chính (PRIMARY KEY) hoặc khoá phụ (UNIQUE) của một bảng dữ liệu khác.
Hình dưới đây cho ta thấy được mối quan hệ giữa 3 bảng DIEMTHI, SINHVIEN và MONHOC. Trong bảng DIEMTHI, MASV là khoá ngoài tham chiếu đến cột MASV của bảng SINHVIEN và MAMONHOC là khoá ngoài tham chiếu đến cột MAMONHOC của bảng MONHOC.
Với mối quan hệ được tạo ra như hình trên, hệ quản trị cơ sở dữ liệu sẽ kiểm tra tính hợp lệ của mỗi bản ghi trong bảng DIEMTHI mỗi khi được bổ sung hay cập nhật. Một bản ghi bất kỳ trong bảng DIEMTHI chỉ hợp lệ (đảm bảo ràng buộc FOREIGN KEY) nếu giá trị của cột MASV phải tồn tại trong một bản ghi nào đó của bảng SINHVIEN
và giá trị của cột MAMONHOC phải tồn tại trong một bản ghi nào đó của bảng MONHOC.
Ràng buộc FOREIGN KEY được định nghĩa theo cú pháp dưới đây:
[CONSTRAINT tên_ràng_buộc]
FOREIGN KEY [(danh_sách_cột)]
REFERENCES tên_bảng_tham_chiếu(danh_sách_cột_tham_chiếu)
[ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT]
[ON UPDATE CASCADE | NO ACTION | SET NULL | SET DEFAULT]
Việc định nghĩa một ràng buộc FOREIGN KEY bao gồm các yếu tố sau:
Tên cột hoặc danh sách cột của bảng được định nghĩa tham gia vào khoá ngoài.
Tên của bảng được tham chiếu bởi khoá ngoài và danh sách các cột được tham chiếu đến trong bảng tham chiếu.
Cách thức xử lý đối với các bản ghi trong bảng được định nghĩa trong trường hợp các bản ghi được tham chiếu trong bảng tham chiếu bị xoá (ON DELETE) hay cập nhật (ON UPDATE). SQL chuẩn đưa ra 4 cách xử lý:
·CASCADE: Tự động xoá (cập nhật) nếu bản ghi được tham chiếu bị xoá (cập nhật).
·NO ACTION: (Mặc định) Nếu bản ghi trong bảng tham chiếu đang được tham chiếu bởi một bản ghi bất kỳ trong bảng được định nghĩa thì bàn ghi đó không được phép xoá hoặc cập nhật (đối với cột được tham chiếu).
·SET NULL: Cập nhật lại khoá ngoài của bản ghi thành giá trị NULL (nếu cột cho phép nhận giá trị NULL).
·SET DEFAULT: Cập nhật lại khoá ngoài của bản ghi nhận giá trị mặc định (nếu cột có qui định giá trị mặc định).
Câu lệnh dưới đây định nghĩa bảng DIEMTHI với hai khoá ngoài trên cột
MASV và cột MAMONHOC (giả sử hai bảng SINHVIEN và MONHOC đã được định nghĩa)
CONSTRAINT pk_diemthi PRIMARY KEY(mamonhoc,masv),
CONSTRAINT fk_diemthi_mamonhoc
FOREIGN KEY(mamonhoc) REFERENCES monhoc(mamonhoc) ON DELETE CASCADE
ON UPDATE CASCADE, CONSTRAINT fk_diemthi_masv
FOREIGN KEY(masv) REFERENCES sinhvien(masv) ON DELETE CASCADE
Cột được tham chiếu trong bảng tham chiếu phải là khoá chính (hoặc là khoá phụ).
Cột được tham chiếu phải có cùng kiểu dữ liệu và độ dài với cột tương ứng trong khóa ngoài.
Bảng tham chiếu phải được định nghĩa trước. Do đó, nếu các bảng có mối quan hệ vòng, ta có thể không thể định nghĩa ràng buộc FOREIGN KEY ngay trong câu lệnh CREATE TABLE mà phải định nghĩa thông qua lệnh ALTER TABLE.
Một bảng sau khi đã được định nghĩa bằng câu lệnh CREATE TABLE có thể được sửa đổi thông qua câu lệnh ALTER TABLE. Câu lệnh này cho phép chúng ta thực hiện được các thao tác sau:
Bổ sung một cột vào bảng.
Thay đổi định nghĩa của một cột trong bảng.
Xoá bỏ hoặc bổ sung các ràng buộc cho bảng Cú pháp của câu lệnh ALTER TABLE như sau: ALTER TABLE tên_bảng
ALTER COLUMN tên_cột kiểu_dữ_liêu [NULL | NOT NULL] | DROP COLUMN tên_cột |
ADD CONSTRAINT tên_ràng_buộc định_nghĩa_ràng_buộc | DROP CONSTRAINT tên_ràng_buộc
Các ví dụ dưới đây minh hoạ cho ta cách sử dụng câu lệnh ALTER TABLE trong các trường hợp.
Giả sử ta có hai bảng DONVI và NHANVIEN với định nghĩa như sau:
madvINTNOT NULLPRIMARY KEY, tendvNVARCHAR(30)NOT NULL
Bổ sung vào bảng NHANVIEN cột DIENTHOAI với ràng buộc CHECK nhằm qui định điện thoại của nhân viên là một chuỗi 6 chữ số:
CONSTRAINT chk_nhanvien_dienthoai
CHECK (dienthoai LIKE '[09][09][09][09][09][09]')
Bổ sung thêm cột MADV vào bảng NHANVIEN:
Định nghĩa lại kiểu dữ liệu của cột DIACHI trong bảng NHANVIEN và cho phép cột này chấp nhận giá trị NULL:
ALTER COLUMN diachi NVARCHAR(100) NULL
Xoá cột ngày sinh khỏi bảng NHANVIEN :
Định nghĩa khoá chính (ràng buộc PRIMARY KEY) cho bảng NHANVIEN là cột MANV:
CONSTRAINT pk_nhanvien PRIMARY KEY(manv)
Định nghĩa khoá ngoài cho bảng NHANVIEN trên cột MADV tham chiếu đến cột MADV của bảng DONVI :
FOREIGN KEY(madv) REFERENCES donvi(madv)
Xoá bỏ ràng buộc kiểm tra số điện thoại của nhân viên
DROP CONSTRAINT CHK_NHANVIEN_DIENTHOAI
Nếu bổ sung thêm một cột vào bảng và trong bảng đã có ít nhất một bản ghi thì cột mới cần bổ sung phải cho phép chấp nhận giá trị NULL hoặc phải có giá trị mặc định.
Muốn xoá một cột đang được ràng buộc bởi một ràng buộc hoặc đang được tham chiếu bởi một khoá ngoài, ta phải xoá ràng buộc hoặc khoá ngoài trước sao cho trên cột không còn bất kỳ một ràng buộc và không còn được tham chiếu bởi bất kỳ khoá ngoài nào.
Nếu bổ sung thêm ràng buộc cho một bảng đã có dữ liệu và ràng buộc cần bổ sung không được thoả mãn bởi các bản ghi đã có trong bảng thì câu lệnh ALTER TABLE không thực hiện được.
Khi một bảng không còn cần thiết , ta có thể xoá nó ra khỏi cơ sở dữ liệu bằng câu lệnh DROP TABLE. Câu lệnh này cũng đồng thời xoá tất cả những ràng buộc, chỉ
mục, trigger liên quan đến bảng đó.
Câu lệnh có cú pháp như sau:
Trong các hệ quản trị cơ sở dữ liệu, khi đã xoá một bảng bằng lệnh DROP TABLE, ta không thể khôi phục lại bảng cũng như dữ liệu của nó. Do đó, cần phải cẩn thận khi sử dụng câu lệnh này.
Câu lệnh DROP TABLE không thể thực hiện được nếu bảng cần xoá đang được tham chiếu bởi một ràng buộc FOREIGN KEY. Trong trường hợp này, ràng buộc FOREIGN KEY đang tham chiếu hoặc bảng đang tham chiếu đến bảng cần xoá phải được xoá trước.
Khi một bảng bị xoá, tất cả các ràng buộc, chỉ mục và trigger liên quan đến bảng cũng đồng thời bị xóa theo. Do đó, nếu ta tạo lại bảng thì cũng phải tạo lại các đối tượng này.
Giả sử cột MADV trong bảng DONVI đang được tham chiếu bởi khoá ngoàifk_nhanvien_madvtrong bảng NHANVIEN. Để xoá bảng DONVI ra khỏi cơ sở dữ liệu, ta thực hiện hai câu lệnh sau:
Xoá bỏ ràng buộcfk_nhanvien_madvkhỏi bảng NHANVIEN:
DROP CONSTRAINT fk_nhanvien_madv
>>>Ngôn ngữ thao tác dữ liệu
>>>Thủ tục lưu trữ và trigger