ผู้เขียนได้รับมอบหมาย ให้เขียนบทความเกี่ยวกับ Constraints ซึ่งผู้เขียนขอเรียกว่า Integrity Constraints
โดยหากแปลกันเป็นคำ ๆ Constraint จะแปลว่า “ข้อบังคับ” หรือ “ข้อจำกัด”
ขณะที่คำว่า Integrity จะแปลว่า “ความถูกต้องสมบูรณ์” หรือ “ความบูรภาพ” ถ้าเอาสองคำนี้มาผสมกันเป็น Integrity Constraints ก็จะได้ความหมายว่า กฎข้อบังคับต่างๆ ที่ทำให้เกิดความถูกต้องสมบูรณ์ (ของข้อมูล)
การบังคับ Integrity Constraints สำหรับฐานข้อมูลเชิงสัมพันธ์ (Relational Database) นั้นจุดเริ่มต้นมาจากเอกสารชื่อ Codd's Twelve Commandments (เป็นกฎ 13 ข้อ นับจาก 0 - 12) ของ Edgar F. Codd ซึ่งเป็นผู้ริเริ่ม Relational Model สำหรับฐานข้อมูล
เอกสารดังกล่าวได้พูดถึงการออกแบบระบบจัดการฐานข้อมูลเชิงสัมพันธ์ (RDBMS ย่อมาจาก Relational Database Management System) ต้องมีอะไรหรือทำอะไรได้บ้าง
ในข้อที่ 5 ของเอกสารระบุไว้ว่าระบบจะต้องมีภาษาที่สามารถกำหนด Integrity Constraints ส่วนในข้อที่ 10 ระบุภาษานั้นจะต้องสร้าง Integrity Constraints ขึ้นและจัดเก็บเอาไว้ได้ใน Data Catalog ไม่ใช่อยู่ในฝั่ง Application
รูปภาพ Edgar F. Codd (1923-2003) ผู้ริเริ่ม Relational Model สำหรับฐานข้อมูล
ปัจจุบันภาษาที่ใช้สร้าง Database Objects (หมายรวมถึง Tables และ Constraints) บน Relational Database คงฟันธงไปได้เลยว่าต้องเป็นภาษา SQL (Structured Query Language) ซึ่งมีมาตรฐานรับรองทั้ง ANSI และ ISO/IEC และอาจมีการพัฒนาเพิ่มเติมจากผู้ผลิตและเรียกภาษาที่ปรับปรุงเพิ่มเติมของตนเองในชื่อใหม่ เช่น Microsoft SQL Server จะเรียกภาษา SQL ของตนเองว่า Transact-SQL หรือ Oracle ก็เรียกภาษา SQL ของตนเองว่า PL-SQL เป็นต้น
ก่อนที่จะรู้จักประเภทต่าง ๆ ของ Constraint โดยละเอียด เราต้องทราบชนิดของ Data Integrity กันก่อน ทั้งนี้ Constraint ประเภทต่าง ๆ มีผลต่อความถูกต้องของข้อมูลต่างกัน
Data Integrity
Data Integrity หรือ ความถูกต้องสมบูรณ์ของข้อมูลจำแนกออกเป็น 3 ชนิดด้วยกัน
- Entity Integrity เพื่อให้แน่ใจว่าข้อมูลที่ใส่เข้าไปมีความสมบูรณ์ถูกต้อง โดยสามารถระบุถึงแถวข้อมูลภายใน Entity (หรือ Table) ได้
- Referential Integrity เพื่อให้แน่ใจว่าข้อมูลที่ใส่เข้าไปมีความสมบูรณ์ถูกต้องของความสัมพันธ์ระหว่าง 2 ตาราง โดยข้อมูลที่ใส่ในตารางจะต้องนำค่ามาจากอีกตารางที่อ้างถึงเสมอ
- Domain Integrity เพื่อให้แน่ใจว่าข้อมูลที่ใส่เข้าไปมีความสมบูรณ์ถูกต้องตามข้อบังคับเดียวกันทั้งโดเมนหรือคอลัมน์นั้นเอง
Integrity Constraints
Microsoft SQL server สามารถกำหนด Constraints ได้หลากหลาย เพื่อให้เกิดความถูกต้องสมบูรณ์แก่ข้อมูล (Data Integrity) โดยผู้เขียนได้จำแนกให้เห็นดังตารางต่อไปนี้
Entity Integrity Referential Integrity Domain Integrity
√
√
√
√
√
√
√
√
ตัวอย่างคำสั่ง CREATE TABLE ใช้ประกอบการอธิบาย Constraints
1 2 3 4 5 6 7 8 9 10 11 12 13CREATE TABLE HR.SimpleEmployees ( EmpID int IDENTITY(1,1) NOT NULL , FirstName nvarchar(30) NOT NULL , LastName nvarchar(30) NOT NULL , Title nvarchar(30) NOT NULL , BirthDate datetime NOT NULL , HireDate datetime NOT NULL , ModifiedDate datetime NOT NULL , EndDate datetime NULL CONSTRAINT PK_Employees PRIMARY KEY (EmpID) , CONSTRAINT AK_EmpName UNIQUE (FirstName, LastName) );
Unique Constraint
ถือเป็นข้อบังคับให้เกิด Entity Integrity เพราะบังคับให้ค่าในแต่ละแถวข้อมูลของคอลัมน์ หรือ กลุ่มของคอลัมน์ที่บังคับด้วย Unique Constraint จะต้องมีค่าไม่ซ้ำกัน (การพิจารณาว่าแถวข้อมูลใดมีค่าซ้ำหรือไม่ เป็นการตรวจและบังคับทั้งตารางจึงถือเป็น Entity Integrity) และ คอลัมน์ดังกล่าวสามารถระบุถึงแถวข้อมูลว่าเป็นแถวข้อมูลใดได้ แม้ว่าบางแถวข้อมูลที่มีค่าเป็น NULL อาจระบุไม่ได้ก็ตาม จากตัวอย่างคำสั่ง CREATE TABLE บรรทัดที่ 12
CONSTRAINT AK_EmpName UNIQUE (FirstName, LastName)
จะเป็นการสร้าง Unique Constraint ชื่อ AK_EmpName เพื่อบังคับให้ค่าผสมจากคอลัมน์ FirstName และ LastName จะต้องไม่ซ้ำกับแถวข้อมูลใดเลยในตารางนี้
Nullability Constraint
ถือเป็นข้อบังคับให้เกิด Domain Integrity เป็นข้อบังคับสำหรับคอลัมน์ เพื่ออนุญาตหรือไม่อนุญาตให้เว้นว่าง(ไม่ใส่ข้อมูล) ในคอลัมน์นั้น ๆ (ระบบจะเก็บเป็นค่า NULL หากเว้นว่างไม่ใส่ค่า)
คอลัมน์ใดไม่อนุญาตให้เว้นว่าง ให้ระบุ NOT NULL ท้ายการประกาศชื่อคอลัมน์ คอลัมน์ใดอนุญาตให้เว้นว่างได้ ให้ระบุ NULL ท้ายการประกาศชื่อคอลัมน์
Primary Key Constraint
ถือเป็นข้อบังคับให้เกิด Entity Integrity มีคุณสมบัติทุกอย่างของ Unique Constraint แต่เพิ่มเงื่อนไขว่าคอลัมน์ หรือกลุ่มของคอลัมน์ จะต้องทำหน้าที่เป็น Unique Identifier ให้กับตาราง พูดง่ายๆก็คือ จะเป็นตัวระบุแถวข้อมูล แต่ละแถวข้อมูล ของตาราง และ เนื่องจากต้องเป็นตัวระบุแถวข้อมูลซึ่งเป็นค่าว่างหรือ NULL ไม่ได้
จากตัวอย่างคำสั่ง CREATE TABLE บรรทัดที่ 11
CONSTRAINT PK_Employees PRIMARY KEY (empid)
จะเป็นการสร้าง Primary Key Constraint ชื่อ PK_Employees ให้กับคอลัมน์ EmpID สรุปความแตกต่างระหว่าง Unique Constraint กับ Primary Key Constraint อย่างง่าย ๆ ก็คือ
- Unique Constraint ห้ามซ้ำกัน แต่เป็นค่าว่างได้
- Primary Key Constraint ห้ามซ้ำกัน และเป็นค่าว่างไม่ได้
Default Constraint
ถือเป็นข้อบังคับให้เกิด Domain Integrity เป็นข้อบังคับสำหรับคอลัมน์ เพื่อกำหนดค่าตั้งต้นให้กับคอลัมน์ หากเว้นว่างไม่ใส่ข้อมูลในคอลัมน์นั้น ๆ
ตัวอย่างคำสั่ง CREATE TABLE ที่มีการกำหนด Default Constraints
CREATE TABLE Sales.Orders ( OrderID int IDENTITY(1,1) NOT NULL , CustID int NOT NULL , Empid int NOT NULL , OrderDate datetime NOT NULL CONSTRAINT DF_OrderDate DEFAULT GETDATE() , RequiredDate datetime NOT NULL , ShippedDate datetime NULL , ModifiedDate datetime NOT NULL CONSTRAINT DF_ModifiedDate DEFAULT GETDATE() , EndDate datetime NULL CONSTRAINT PK_Orders PRIMARY KEY(OrderID) )
จะเห็นว่าคอลัมน์ OrderDate และ ModifiedDate มีการกำหนด Default Constraint ให้ใช้วันเวลาปัจจุบันหากไม่ใส่ข้อมูลลงในคอลัมน์
สามารถเข้าไปสร้างและแก้ไข Default Constraint ผ่านทาง Properties ของคอลัมน์ได้ดังรูป
เมื่อผู้เขียนทำการ Insert ข้อมูลด้วยคำสั่งต่อไปนี้
INSERT INTO Sales.Orders (CustID,Empid,OrderDate,RequiredDate,ModifiedDate) VALUES (1,1,DEFAULT,'Aug 5, 2016',DEFAULT)
และทำการสืบค้นข้อมูลจากตารางดังกล่าวได้ข้อมูลดังนี้
โดยคอลัมน์ที่ไม่ระบุไว้ในคำสั่ง Insert มีการกำหนดค่าตาม
- OrderID ได้จาก Identity Property (เหมือน Auto Number ใน Access)
- OrderDate ได้จาก Default Constraint
- ShippedDate กำหนดค่า NULL จาก Nullability
- ModifiedDate ได้จาก Default Constraint
- EndDate กำหนดค่า NULL จาก Nullability
Foreign Key Constraint
ถือเป็นข้อบังคับให้เกิดทั้ง Referential Integrity และ Domain Integrity คือ เป็นข้อบังคับสำหรับคอลัมน์ที่เป็น Foreign Key ให้ใส่ค่าในคอลัมน์ได้ เฉพาะค่าจากคอลัมน์ที่เป็น Primary Key ในตารางที่มีความสัมพันธ์กันเท่านั้น
ตัวอย่างคำสั่ง CREATE TABLE ที่มีการกำหนด Foreign Key Constraint
CREATE TABLE Sales.Orders ( OrderID int IDENTITY(1,1) NOT NULL , CustID int NOT NULL , EmpID int NOT NULL , OrderDate datetime NOT NULL CONSTRAINT DF_OrderDate DEFAULT GETDATE() , RequiredDate datetime NOT NULL , ShippedDate datetime NULL , ModifiedDate datetime NOT NULL CONSTRAINT DF_ModifiedDate DEFAULT GETDATE() , EndDate datetime NULL CONSTRAINT PK_Orders PRIMARY KEY(OrderID) , CONSTRAINT FK_Orders_Employees FOREIGN KEY (EmpID) REFERENCES HR.SimpleEmployees (EmpID) )
จากคำสั่งข้างบนจะได้ความสัมพันธ์ระหว่างตาราง SimpleEmployees กับ Orders ขึ้น โดยที่คอลัมน์ EmpID ในตาราง SimpleEmployees เป็น Primary Key Constraint และคอลัมน์ EmpID ในตาราง Orders เป็น Foreign Key Constraint
รูปแสดงความสัมพันธ์ระหว่างตาราง SimpleEmployees กับ ตาราง Orders
เมื่อผู้เขียนทำการ Insert ข้อมูล ด้วยคำสั่งต่อไปนี้
INSERT INTO HR.SimpleEmployees (FirstName,LastName,Title,BirthDate,HireDate,ModifiedDate) VALUES ('Phakkhaphong','Krittawat','Mr.','Aug 5,1974',GETDATE(),GETDATE()) , ('Somchai','Jaidee','Mr.','Jan 8,1980',GETDATE(),GETDATE()) , ('Somsri','Rakchad','Ms.','Sep 13,1987',GETDATE(),GETDATE());
และทำการสืบค้นข้อมูลจากตารางดังกล่าวได้ข้อมูลดังนี้
และทำการสืบค้นข้อมูลจากตารางดังกล่าวได้ข้อมูลดังนี้
INSERT INTO Sales.Orders (CustID,Empid,OrderDate,RequiredDate,ModifiedDate) VALUES (1,4,DEFAULT,'Aug 5, 2016',DEFAULT);
ก็จะได้ผลลัพธ์ดังต่อไปนี้
Msg 547, Level 16, State 0, Line 1 The INSERTคำสั่งconflicted with the FOREIGN KEY constraint "FK_Orders_Employees". The conflict occurred in database "TestDB", table "HR.SimpleEmployees", column 'EmpID'. Theคำสั่งhas been terminated.
แสดงให้เห็นว่าการใส่ค่า 4 ให้แก่คอลัมน์ EmpID ในตาราง Orders นั้นขัดแย้งกับ Foreign Key Constraint เพราะไม่มีแถวข้อมูลใดในตาราง SimpleEmployees ที่ค่าในคอลัมน์ EmpID เท่ากับ 4 เลย
Check Constraint
ถือเป็นข้อบังคับให้เกิด Domain Integrity หรือ ให้เกิดทั้ง Domain Integrity พร้อมกับ Referential Integrity เหมือน Foreign Key Constraint ก็ได้ ขึ้นอยู่กับการเขียน Expression
Check Constraint ทำหน้าที่เป็น Boolean Expression โดยพิจารณาค่าที่ใส่เข้ามา หากตรวจสอบแล้วเป็น False หรือหาค่าไม่ได้ ก็จะปฏิเสธค่านั้น แต่หากเป็น True ก็จะยอมรับค่านั้น
ตัวอย่าง กรณี Check Constraint เป็นข้อบังคับให้เกิด Domain Integrity
ตัวอย่างคำสั่ง CREATE TABLE ที่มีการกำหนด Check Constraint
CREATE TABLE HR.SimpleEmployees ( EmpID int IDENTITY(1,1) NOT NULL , FirstName nvarchar(30) NOT NULL , LastName nvarchar(30) NOT NULL , Title nvarchar(30) NOT NULL , Salary money NOT NULL , BirthDate datetime NOT NULL , HireDate datetime NOT NULL , ModifiedDate datetime NOT NULL , EndDate datetime NULL CONSTRAINT PK_Employees PRIMARY KEY (EmpID) , CONSTRAINT AK_EmpName UNIQUE (FirstName, LastName) , CONSTRAINT CK_Salary CHECK (Salary < 100000) );
หรือสามารถเข้าไปสร้างและแก้ไข Check Constraint ผ่านทาง SSMS ได้ดังรูป
และเมื่อผู้เขียนทำการ Insert ข้อมูล ด้วยคำสั่งต่อไปนี้
INSERT INTO HR.SimpleEmployees (FirstName,LastName,Title,Salary,BirthDate,HireDate,ModifiedDate) VALUES ('Phakkhaphong','Krittawat','Mr.',150000,'Aug 5,1974',GETDATE(),GETDATE())
ก็จะได้ผลลัพธ์ดังต่อไปนี้
Msg 547, Level 16, State 0, Line 1 The INSERTคำสั่งconflicted with the CHECK constraint "CK_Salary". The conflict occurred in database "TestDB", table "HR.SimpleEmployees", column 'Salary'. Theคำสั่งhas been terminated.
แสดงให้เห็นว่าการใส่ค่า 150000 ให้แก่คอลัมน์ Salary ในตาราง SimpleEmployees ทำให้ Boolean Expression ใน Check Constraint ได้ค่าเป็น False ทำให้ปฏิเสธค่าดังกล่าว และทำให้ Insert ไม่สำเร็จ
แต่หาก Insert ข้อมูล ด้วยคำสั่งต่อไปนี้
INSERT INTO HR.SimpleEmployees (FirstName,LastName,Title,Salary,BirthDate,HireDate,ModifiedDate) VALUES ('Phakkhaphong','Krittawat','Mr.',90000,'Aug 5,1974',GETDATE(),GETDATE()) , ('Somchai','Jaidee','Mr.',85000,'Jan 8,1980',GETDATE(),GETDATE()) , ('Somsri','Rakchad','Ms.',85000,'Sep 13,1987',GETDATE(),GETDATE());
และทำการสืบค้นข้อมูลจากตารางดังกล่าวได้ข้อมูลดังนี้
ทั้ง 3 แถวข้อมูลก็จะสามารถ Insert ได้ เพราะแต่ละแถวข้อมูลเมื่อผ่าน Boolean ใน Check Constraint แล้วมีค่าเป็น TRUE
ตัวอย่าง กรณี Check Constraint เป็นข้อบังคับให้เกิดทั้ง Domain Integrity พร้อมกับ Referential Integrity
กรณีนี้ผู้เขียนจะใช้ Function ร่วมด้วย ดังคำสั่งต่อไปนี้
CREATE FUNCTION HR.udfCheckEmpID(@EmpID INT) RETURNS TINYINT AS BEGIN IF EXISTS ( SELECT * FROM HR.SimpleEmployees WHERE EmpID=@EmpID ) RETURN 1 RETURN 0 END GO
เป็น Function ที่รับพารามิเตอร์เข้าชื่อ @EmpID แล้วนำไปสืบค้นในตาราง SimpleEmployees ว่ามีแถวข้อมูลใดในคอลัมน์ EmpID มีค่าตรงกับ @EmpID
ถ้ามีแม้เพียงแถวข้อมูลเดียวก็ให้คืนค่าเป็น 1 แต่หากไม่พบก็ให้คืนค่าเป็น 0
จากนั้นนำ Function นี้ไปใช้ใน Check Constraint ดังคำสั่ง CREATE TABLE ต่อไปนี้
CREATE TABLE Sales.Orders ( OrderID int IDENTITY(1,1) NOT NULL , CustID int NOT NULL , EmpID int NOT NULL , OrderDate datetime NOT NULL CONSTRAINT DF_OrderDate DEFAULT GETDATE() , RequiredDate datetime NOT NULL , ShippedDate datetime NULL , ModifiedDate datetime NOT NULL CONSTRAINT DF_ModifiedDate DEFAULT GETDATE() , EndDate datetime NULL CONSTRAINT PK_Orders PRIMARY KEY(OrderID) , CONSTRAINT CK_EmpID CHECK (HR.udfCheckEmpID(EmpID)=1) )
และเมื่อผู้เขียนทำการ Insert ข้อมูล ด้วยคำสั่งต่อไปนี้
INSERT INTO Sales.Orders (CustID,Empid,OrderDate,RequiredDate,ModifiedDate) VALUES (1,7,DEFAULT,'Aug 5, 2016',DEFAULT)
ก็จะได้ผลลัพธ์ดังต่อไปนี้
Msg 547, Level 16, State 0, Line 1 The INSERTคำสั่งconflicted with the CHECK constraint "CK_EmpID". The conflict occurred in database "TestDB", table "Sales.Orders", column 'EmpID'. Theคำสั่งhas been terminated.
แสดงให้เห็นว่าการใส่ค่า 7 ให้แก่คอลัมน์ EmpID ในตาราง Orders ทำให้ Check Constraint ทำงานโดยส่ง 7 เป็นพารามิเตอร์เข้า ของ Function ชื่อ udfCheckEmpID แต่ไม่พบแถวข้อมูลในตาราง SimpleEmployees ที่ EmpID เท่ากับ 7 เลย ทำให้ Function คืนค่าเป็น 0 ออกมา ทำให้ Boolean มีค่าเป็น False และปฏิเสธค่าดังกล่าว และทำให้ Insert ไม่สำเร็จ
แต่หาก Insert ข้อมูล ด้วยคำสั่งต่อไปนี้
INSERT INTO Sales.Orders (CustID,Empid,OrderDate,RequiredDate,ModifiedDate) VALUES (1,5,DEFAULT,'Aug 5, 2016',DEFAULT)
ผลลัพธ์ที่ได้คือ
(1 row(s) affected)
แสดงให้เห็นว่าเราสามารถใช้ Check Constraint ในลักษณะนี้แทน Foreign Key Constraint ได้ และทำให้ Check Constraint ลักษณะนี้เป็นข้อบังคับให้เกิดทั้ง Domain Integrity พร้อมกับ Referential Integrity ได้เช่นกัน
Data Types
ยังมีอีกสิ่งที่ถือเป็นข้อบังคับให้เกิด Domain Integrity นั้นก็คือ การประกาศ Data Types ให้กับคอลัมน์จะเป็นการบังคับให้ข้อมูลต้องมี Data Type หรือ ชนิดข้อมูลเดียวกันทั้งคอลัมน์
นอกเหนือจากนั้นในมาตรฐาน ANSI SQL89 และ ANSI SQL92 ยังมีคำสั่งที่สามารถสร้างชนิดข้อมูลขึ้นมาใช้งานเอง (User-Defined Data Type) ได้
คำสั่งนั้นคือ CREATE DOMAIN จะเห็นการใช้คำว่า DOMAIN เลยเพื่อสื่อถึงการบังคับให้มีชนิดข้อมูลเดียวกันทั้งคอลัมน์ ตัวอย่างคือ
CREATE DOMAIN InternetSalesPrice AS DECIMAL(5,2)
คำสั่งดังกล่าวไม่มีใช้ใน Microsoft SQL Server แต่ Microsoft SQL Server ก็สามารถสร้างชนิดข้อมูลขึ้นมาใช้งานเองได้ผ่านคำสั่ง CREATE TYPE ดังตัวอย่าง
CREATE TYPE Sales.InternetSalesPrice FROM decimal(5, 2) NOT NULL
แล้วจึงนำ User-Defined Data Type ที่สร้างขึ้นไปกำหนดให้กับคอลัมน์ของตารางที่สร้างขึ้นอีกที ดังตัวอย่าง