รู้จักกับ Constraints

ผู้เขียนได้รับมอบหมาย ให้เขียนบทความเกี่ยวกับ 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 

คอลัมน์ใดไม่อนุญาตให้เว้นว่าง ให้ระบุ NOT NULL ท้ายการประกาศชื่อคอลัมน์
คอลัมน์ใดอนุญาตให้เว้นว่างได้ ให้ระบุ  NULL ท้ายการประกาศชื่อคอลัมน์

Primary Key Constraint

ถือเป็นข้อบังคับให้เกิด Entity Integrity มีคุณสมบัติทุกอย่างของ Unique Constraint
แต่เพิ่มเงื่อนไขว่าคอลัมน์ หรือกลุ่มของคอลัมน์ จะต้องทำหน้าที่เป็น Unique Identifier ให้กับตาราง
พูดง่ายๆก็คือ จะเป็นตัวระบุแถวข้อมูล แต่ละแถวข้อมูล ของตาราง
และ เนื่องจากต้องเป็นตัวระบุแถวข้อมูลซึ่งเป็นค่าว่างหรือ NULL ไม่ได้

จากตัวอย่างคำสั่ง CREATE TABLE บรรทัดที่ 11

 CONSTRAINT PK_Employees PRIMARY KEY (empid)

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)
)

เมื่อผู้เขียนทำการ 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 เลย
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.

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());

และทำการสืบค้นข้อมูลจากตารางดังกล่าวได้ข้อมูลดังนี้