SQL Constraints

Do you know The Constraints ?? รู้จัก Constraints กันหรือเปล่า

so then what's the "constraints" ?? แล้ว อะไรคือ Constraints ล่ะ

Constraints คือ ข้อจำกัด หรือ ข้อบังคับ หรือ เงื่อนไข ของการเก็บข้อมูลนั่นเอง ถ้าเราได้ลองมองดูดีๆแล้วจะพบว่า ถ้าในการเก็บข้อมูลของเรา ไม่มีการกำหนดสิ่งเหล่านี้ไว้...จะเกิดปัญหาต่อ DB ของเราในอนาคตภายภาคหน้าหรือเปล่า เช่น หาคีย์ไม่เจอ ค่าอะไรก็ไม่รู้ บลาๆๆ

แต่ไม่ต้องกังวลอะไรทั้งนั้น เพราะวันนี้เราจะมาเรียนรู้กันในเรื่องนี้ไปด้วยกัน อันดับแรก ไปดู Cover Content ก่อนเลยดีกว่าคร้าบ

Cover Content เนื้อหาภายใน Content นี้

• Part 1: Constraints = Limitation ??
• Part 2: Tiny things of my selected DBMS เกร็ดเล็กน้อยยยย

 

Part 1: Constraints = Limitation ??

 ส่วนแรกเลยก่อนที่จะพิสูจน์สมการดังกล่าวข้างต้น จะอธิบายก่อนว่า Constraints มีอะไรบ้าง ซึ่งก็จะมีดังนี้

• NOT NULL - column cannot store NULL value 
• UNIQUE - each row for a column must have a unique value 
• PRIMARY KEY - column have a unique identity 
• FOREIGN KEY - the referential integrity of the data in one table to match values in another table 
• CHECK - the value in a column meets a specific condition 
• DEFAULT - specifies a default value for a column

ไม่เยอะเลยนะครับ สำหรับข้อจำกัดเหล่านั้น แล้วมันอยู่ตรงไหนล่ะ ของ SQL ???

นี่เลยครับ Syntax คร่าวๆให้มองภาพออก

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

สังเกตตรง Constraints Name นะครับตรงนั้นแหละที่เราจะไปดูกันต่อไป ส่วนวิธีการใช้งานสิ่งเหล่านี้ ก็จะนำเสนอดังต่อไปนี้ครับ

NOT NULL เป็นการกำหนดให้ data จะต้องไม่เก็บเป็น NULL นะครับ
CHECK คือการตรวจสอบเงื่อนไขก่อน ซึ่งก่อนที่จะเก็บข้อมูลลงใน database ก็ต้องผ่านเงื่อนไขนี้เท่านั้น
DEFAULT เป็นการกำหนดค่าดั้งเดิมให้กับค่า Column นั้นๆ หากไม่ได้กำหนดตอน Add Data

CREATE TABLE Persons
(
ID int NOT NULL,
Name varchar(20) NOT NULL,
Surname varchar(20),
Gender varchar(1) DEFAULT 'M',
CHECK (ID>0)
);

เมื่อลองรันดู ผลลัพธ์จะได้

อธิบาย: ในที่นี้เราจะสร้าง Table ชื่อว่า Persons มาใหม่มีข้อมูลเป็น
- ID: INT ใช้เงื่อนไข CHECK > 0 และ NOT NULL
- Name: VARCHAR ขนาด 20 ตัวอักษร และ NOT NULL
- Surname: VARCHAR ขนาด 20 ตัวอักษร
- Gender: VARCHAR ขนาด 1 ตัวอักษร โดยกำหนด DEFAULT คือ M (Male)


ลำดับต่อไปจะลองทดสอบกันดู
กรณีแรก ID=NULL, Name="first", Surname="one", Gender="F"

insert into persons(Name,Surname,Gender) value ('first','one','F');

ผลลัพธ์ คือส่วนของ Constraint NOT NULL ครับผม

กรณีสอง ID=-2, Name="second", Surname="two", Gender="F"

insert into persons(ID,Name,Surname,Gender) value (-2,'second','two','F');

ผลลัพธ์ คือส่วนของ Constraint CHECK ครับผม

หมายเหตุ: ทาง MySQL ได้อธิบายว่า "The CHECK clause is parsed but ignored by all storage engines." นะครับผม โดยสามารถหาอ่านได้ใน CREATE_TABLE Doc

กรณีสาม ID=3, Name="third", Surname="three", Gender=NULL

insert into persons(ID,Name,Surname) value (3,'third','three');

ผลลัพธ์ คือส่วนของ Constraint DEFAULT ครับผม

ส่วนต่อไปจะเป็นการแนะนำ Constraints UNIQUE, PRIMARY KEY นะครับ

โดยจะขอกล่าวถึง UNIQUE ก่อนว่าส่วนนี้ จะเป็นการบ่งบอกว่า ข้อมูลในแต่ละ entry นั้นมีความ Unique ของตัวเอง หรือมองได้ง่ายๆว่า ไม่มีใครซ้ำนั้นเองครับ ส่วนของ PRIMARY KEY จะเป็นเหมือน key ที่เป็นหลัก กล่าวคือ มีค่าเป็น NULL ไม่ได้นั้นเอง (ได้ Constraint ของ NOT NULL ด้วยเป็นไงๆๆ)

เพื่อเป็นการไม่เสียเวลา เรามาลองกันเลยดีกว่าครับ โดยอันดับแรกให้ทำการสร้าง Table ใหม่ โดยเราจะกำหนด Primary Key และ Unique Column ด้วยนะครับ

create table members(member_id varchar(3) primary key, name varchar(20),phone_number varchar(10) unique);
insert into members(member_id,name,phone_number) value ('001','Alice','0834567890');
insert into members(member_id,name,phone_number) value ('002','Bob','0809876543');

และทำการใส่ data ไปสัก 2 entry ผลลัพธ์จะได้ดังรูป

เมื่อเราพยายามลองขัดขืน ในการยัด data ด้วยการขัดต่อ Constraints ด้วย Code นี้

insert into members(member_id,name,phone_number) value ('001','Cathy','0855555555'); 
insert into members(member_id,name,phone_number) value ('004','Douge','0809876543');

ก็จะได้ผลดังนี้ครับ

อธิบาย: ใน Code จะถูกประกอบด้วย PRIMARY KEY และ UNIQUE นะครับ ถ้าเราพยายามยัด data เข้าไป จะเกิด ERROR เป็น Duplicate Entry นะครับ ซึ่งผลก็จะเป็นของ Constraint PRIMARY KEY กับ Constraint UNIQUE ตามลำดับ

จากนั้นเราลองไปดู FOREIGN KEY กันต่อเลย โดย Foreign Key จะเป็นตัวอ้างอิง Relation หรือความสัมพันธ์ระหว่าง 2 ความสัมพันธ์น่ะครับ เป็น Reference ระหว่าง Key นั้นเองงงงง มาลองดูกัน

อันดับแรกให้สร้างตารางขึ้นมาใหม่ และทำการ Add ข้อมูลเพิ่มเข้าไปในตารางเดิม (members)

create table membersdetail(id varchar(3), age int, height int, weight int,foreign key(id) references members(member_id));
insert into members(member_id,name,phone_number) value ('003','Cathy','0819871472');
insert into members(member_id,name,phone_number) value ('004','Douge','0895172253');

ผลการทดลอง

และลอง Add ข้อมูลในตารางใหม่ (membersdetail) เข้าไป

insert into membersdetail(id,age,height,weight) value ('002',19,168,62);
insert into membersdetail(id,age,height,weight) value ('005',24,175,68);

ผลลัพธ์

อธิบาย: สาเหตุที่ ข้อมูล entry แรก ใส่เข้าไปได้เพราะมีการ Reference หรืออ้างอิง id ของตารางใหม่ ที่มีค่าอยู่ใน member_id กล่าวคือ ถ้าในตารางแรก (members) มี id ที่ 3 อยู่เมื่อเราจะ Add entry เข้าไปในตารางใหม่ (membersdetail) จำเป็นต้องใช้ id ที่มีอยู่ใน id ของตารางแรก ซึ่งในที่นี้คือ 3 เช่นเดียวกัน หากแต่ว่าถ้าเรา Add entry เข้าไปใหม่ แต่ id ไม่มีอยู่ในตารางแรก ก็จะไม่สามารถ Add ค่าเข้าไปได้ (หรือการ Update) จึงเป็นที่มาของ Constraint FOREIGN KEY ครับผม

Part 2: Tiny things of my selected DBMS เกร็ดเล็กน้อยยยย

ส่วน part นี้นะครับ จะเป็นการบอกอะไรเล็กๆน้อยๆ เกี่ยวกับ DBMS ที่ผมเลือกใช้ กับ 3 คำถามที่จะถามและตอบในโพสต์นี้ ซึ่งในที่นี้ DBMS ที่ผมเลือกใช้ก็คือ MySQL นะครับ มาดูกันครับว่าจะมีอะไรบ้าง

1) Q: What's constraints of MySQL? ใน MySQL นั้นมี Constraints อะไรบ้าง??
A: มีเหมือนข้างต้นที่กล่าวเอาไว้ และนอกจาก Constraints ที่กล่าวมาข้างต้นแล้วนั้น ยังมี Constraints เพิ่มขึ้นมาอีก นั่นก็คือ ENUM และ SET 

โดย ENUM จะเป็นเหมือนตัวเลือก เช่น 'High', 'Medium', 'Low' ซึ่งในการ Add entry จะสามารถเลือก ได้เพียงแค่ 3 ค่านี้เท่านั้น

และ SET ก็คล้ายๆกับ ENUM แต่เราสามารถเลือกได้เป็นแบบ Set เลยนั้นเอง เช่น 'S1, S2, S5'

2) Q: How specify them? เราจะกำหนดมันอย่างไร
A: จากข้างต้น เรามีการกำหนด Constraints ต่างๆไปแล้ว เหลือแต่ ENUM และ SET ซึ่งจะเป็นดังนี้

ENUM specify:

CREATE TABLE Shops(Id INTEGER, Name VARCHAR(55), Quality ENUM('High', 'Average', 'Low'));

SET specify: 

CREATE TABLE Students(Id INTEGER, Name VARCHAR(55), Certificates SET('A1', 'A2', 'B1', 'C1')); 

3) Q: Give an Example? ลองยกตัวอย่างให้ดูหน่อย
A: สำหรับ ENUM

mysql> INSERT INTO Shops VALUES(1, 'Boneys', 'High');
mysql> INSERT INTO Shops VALUES(2, 'AC River', 'Average');
mysql> INSERT INTO Shops VALUES(3, 'AT 34', '**');
mysql> SELECT * FROM Shops;
+------+----------+-----------+
| Id     | Name   | Quality   |
+------+----------+-----------+
|    1   | Boneys  | High      |
|    2   | AC River| Average |
|    3   | AT 34    |             |
+------+----------+-----------+

และสำหรับ SET จะได้

mysql> INSERT INTO Students VALUES(1, 'Paul', 'A1,B1');
mysql> INSERT INTO Students VALUES(2, 'Jane', 'A1,B1,A2');
mysql> INSERT INTO Students VALUES(3, 'Mark', 'A1,A2,D1,D2');
mysql> SELECT * FROM Students;
+------+--------+--------------+
| Id     | Name | Certificates |
+------+--------+--------------+
|    1   | Paul   | A1,B1        |
|    2   | Jane  | A1,A2,B1    |
|    3   | Mark  | A1,A2        |
+------+--------+--------------+

ขอขอบคุณ Thanks:
1) MySQL Constraints
2) W3Schools.com
3) Hilite
4) CREATE_TABLE MySQL Document