CONSTRAINT MySQL : ข้อจำกัด

CONSTRAINT MySQL 

     คือ ข้อจำกัดที่ถูกใช้ในการระบุกฏและข้อบังคับของข้อมูลในตาราง ที่จะถูกจัดเก็บในลำดับต่อไป
โดยมี constraint ที่รองรับ ดังนี้

1) NOT NULL
2) ๊CHECK*
3) DEFAULT
4) UNIQUE
5) PRIMARY KEY
6) FOREIGN KEY (Referential Integrity constraint)

Detail for each constraint 

• NOT NULL

          ใช้สำหรับ กำหนดให้การใส่ข้อมูลในcolumn นั้นๆจะไม่ยอมรับค่าที่เป็น null ได้

          Syntax :

                    CREATE TABLE table_name ( column_name column_type NOT NULL);

          

          Example :

                    เช่น ใช้ในกรณีของการใช้เก็บข้อมูล ชื่อ และนามสกุลของลูกค้า โดยกำหนดว่า ห้ามเป็น null หรือค่าว่าง แต่สำหรับอายุ จะไม่ใส่ก็ได้ 

ภาพแสดงตัวอย่างตารางข้อมูลที่ได้ออกแบบไว้

                              

ภาพแสดงคำสั่งที่ใช้ในการสร้างตาราง

                    พอใช้คำสั่ง describe customer; เพื่อดูรายละเอียดของตาราง จะเห็นได้ว่า  รายละเอียดของตารางของattribute name กับ surname ไม่ยอมรับการใส่null คือ ตรงคอลัมน์ null มีค่าเป็น no

ภาพแสดงรายละเอียดของตาราง

                    ตัวอย่างการใส่ข้อมูล โดยมีอยู่ 3 กรณี ที่เราจะไม่สามารถใส่ข้อมูล

                        1) ใส่แต่อายุ

                        2) ใส่ชื่อกับอายุ

                        3) ใส่นามสกุลกับอายุ

ตัวอย่างการใส่ข้อมูลแต่อายุ

ตัวอย่างการใส่ข้อมูลแต่ชื่อและอายุ

ตัวอย่างการใส่ข้อมูลแต่นามสกุลและอายุ

                    แต่เราสามารถใส่ข้อมูลชื่อกับนามสกุล โดยไม่ใส่อายุได้ เนื่องจากข้อมูลอายุเราไม่ได้กำหนดว่า ห้ามเป็น null

ภาพแสดงตัวอย่างการใส่ข้อมูลชื่อกับนามสกุล โดยไม่ใส่อายุ

ภาพแสดงตัวอย่างการใส่ข้อมูลครบถ้วน

                    จากนั้นทำการเช็คข้อมูลที่ได้เพิ่มเข้าไป โดยใช้คำสั่ง select * from customer;  จะเห็นได้ว่าข้อมูลแถวแรก ตรงคอลัมน์ age เป็นค่า null เนื่องจากไม่มีการใส่ค่าจากผู้ใช้

ภาพแสดงตารางข้อมูล

             

• CHECK*

          สาเหตุที่ทำการใส่ดอกจันทร์สีแดง เนื่องจาก ทางเว็บไซต์ w3schools ได้มีตัวอย่างการใช้ constraint check แต่พอทำตามแล้ว กลับไม่เป็นไปอย่างที่คิดไว้ จึงได้ทำการศึกษาข้อมูลเพิ่มเติม จึงได้คำตอบว่า "check constraints are not supported in MySQL" นั่นก็คือ MySQL ไม่รองรับ constraint check อ้างอิงข้อมูลจาก http://oak.cs.ucla.edu/cs143/project/mysql/constraints.html

          ตัวอย่างที่ทำ คือ สร้างตารางชื่อ persons เก็บข้อมูลชื่อกับอายุ โดยอายุที่ใส่ กำหนดให้มากกว่า 18 

ภาพแสดงการสร้างตาราง โดยใช้constraint check

          ซึ่งถ้าเป็นไปตามที่เขียน การใส่ข้อมูลที่ทำให้เงื่อนไขเป็นเท็จน่าจะใส่ข้อมูลไม่ได้ แต่นี่ ไม่มีการขึ้น error แจ้งเตือนเลย แถมยังบอกว่ามีการเปลี่ยนแปลง1 แถวอีกด้วย

ภาพแสดงการเพิ่มข้อมูล ซึ่งทำให้เงื่อนไขเป็นเท็จ

          จากนั้นลองทำการเช็คข้อมูลในตาราง พบว่าข้อมูลถูกจัดเก็บจริง ซึ่งในความเป็นจริง ไม่ควรถูกจัดเก็บในตาราง

ภาพแสดงข้อมูลในตาราง

โดยข้อมูลการศึกษาตัวอย่าง อ้างอิงจาก http://www.w3schools.com/sql/sql_check.asp

ภาพตัวอย่างการใช้constraint check จากเว็บไซต์

• DEFAULT

          เป็นการกำหนด default ให้กับค่าที่ใส่ในตาราง ในกรณีที่ไม่ได้ใส่ข้อมูล

          Syntax :

               CREATE TABLE table_name

(column_name column_type DEFAULT 'default_value' );

          Example :

               เช่น ใส่ข้อมูลเกี่ยวกับเด็ก โดยในตารางมีการใส่ชื่อกับเพศ โดยเพศของเด็กในกรณีที่ไม่ใส่ จะถือว่ามีค่าเป็นเด็กผู้ชาย หรือ male นั่นเอง

ตัวอย่างตารางที่ได้ทำการออกแบบไว้

ภาพแสดงตัวอย่างคำสั่งการสร้างตารางโดยใช้ default

               ดูรายละเอียดของตารางโดยใช้คำสั่ง describe children; จะเห็นได้ว่าตรงค่า default ของ gender มีค่าเป็น male

ภาพแสดงรายละเอียดของตาราง

               ทดลองใส่ข้อมูล โดยการใส่ข้อมูลเป็นไปตามตารางด้านบนที่ได้ออกแบบไว้ โดยข้อมูลตัวที่สองจะไม่ใส่ค่า gender เพื่อดูการทำงานของ default

เพิ่มข้อมูลชุดแรก โดยให้เพศเป็นผู้หญิง

 

 เพิ่มข้อมูลชุดแรก โดยไม่ใส่เพศ 

เพิ่มข้อมูลชุดที่สาม โดยให้เพศเป็นผู้ชาย ซึ่งเหมือนกับค่า default

ภาพแสดงตารางข้อมูล โดยเป็นไปตามตารางที่ออกแบบไว้

• UNIQUE

          ใช้ในกำหนดว่าการใส่ค่าข้อมูลในคอลัมน์นั้นจะไม่ยอมรับ ค่าข้อมูลที่มีค่าซ้ำกัน ซึ่งจะแตกต่างจากconstraint ที่เป็น PRIMARY KEY (จะได้ศึกษาในลำดับถัดไป) ตรงที่ UNIQUE นั้นจะไม่มีส่วนเกี่ยวข้องใดๆกับ FOREIGN KEY และคอลัมน์ที่มี constraint เป็น unique จะสามารถมีค่าข้อมูลเป็น NULL ได้ โดยค่าข้อมูลที่เป็น NULL นั้นมีความหมายที่ไม่ซ้ำกัน

          Syntax :

               CREATE TABLE table_name                     (column_name column_type UNIQUE);

          Example :

               เช่น การเก็บข้อมูล username กับ ชื่อและนามสกุล โดยมีแนวคิดที่ว่า 1 คนสามารถมีได้หลาย username แต่ username จะไม่มีทางซ้ำกัน  จึงกำหนดให้ attribute username มีconstraint เป็น unique 

ภาพแสดงตัวอย่างตารางที่ได้ออกแบบไว้

ภาพตัวอย่างการสร้างตารางโดยกำหนด constraint unique

               ทดลองการใส่ข้อมูล โดยใส่ข้อมูลตามตารางที่ได้ออกแบบไว้ โดยสามข้อมูลแรกที่ใส่ สามารถใส่ได้ปกติ แต่เมื่อถึงการใส่ข้อมูลตัวที่ 4 จะมีการแจ้งเตือน เนื่องจากข้อมูลตัวที่ 4 ที่ตำแหน่งของ username มีค่าที่ซ้ำกับค่าที่ใส่ไปก่อนหน้านี้ ซึ่งค่าusername ไม่สามารถซ้ำกันได้ เนื่องจากถูกตั้งให้เป็น unique

ภาพแสดงการใส่ข้อมูลตัวที่ 1-3 ตามลำดับ

ภาพแสดงการเกิด error เมื่อใส่ข้อมูลตัวที่ 4 ที่มีค่า username ซ้ำ

               เมื่อดูที่ตารางข้อมูล จะเห็นได้ว่า ในตารางจะไม่มีข้อมูลตัวที่ 4 อยู่เลย

ภาพแสดงตารางข้อมูล

PRIMARY KEY & FOREIGN KEY

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

ภาพแสดงความสัมพันธ์ของทั้งสองตารางที่มีการอ้างอิงกัน

• PRIMARY KEY

          ซึ่ง Primary key จะมีคุณสมบัติ UNIQUE (นั่นคือไม่สามารถใส่ข้อมูลซ้ำกับข้อมูลเดิมได้) และ NOT NULL (สาเหตุที่เป็น not null เพื่อใช้ในการค้นหา) และจะใช้งานร่วมกับ Foreign key

          Syntax :

               CREATE TABLE table_name                     (column_name column_type,
                      PRIMARY KEY ( column_name));

          Example :
               เช่น การสร้างตารางเก็บชื่อย่อของภาควิชา,ความหมายเต็ม และสถานที่ตั้ง โดย primary key ที่แสดงในรูปแบบตาราง จะถูกขีดเส้นใต้ เพื่อบ่งบอก constraint ของมัน 

 ภาพแสดงตารางที่ได้ออกแบบไว้ โดยใช้ primary key

               ซึ่งในตารางที่ได้ออกไว้ primary key คือ n_department 

ภาพแสดงตัวอย่างการสร้างตาราง

               ทำการใส่ข้อมูลตามที่ได้ออกแบบในตอนต้นทั้งสองข้อมูล แต่เมื่อทำการใส่ข้อมูลที่สาม ที่กำหนดให้ชื่อภาควิชาซ้ำกับของเดิมที่มีอยู่คือ ใส่ IE ซึ่งซ้ำกับข้อมูลตัวที่ 2 จึงมีการแจ้งเตือนerror ดังนี้

ภาพแสดงการใส่ข้อมูลตัวที่ 1 และ 2 ที่ไม่เกิดปัญหา

ภาพแสดงการใส่ข้อมูลตัวที่ 3 เพื่อทดลองการแจ้งเตือนเมื่อข้อมูลซ้ำ

ภาพแสดงตารางข้อมูล

• FOREIGN KEY

          จะถูกใช้งานร่วมกันกับ primary key เพื่อสร้างความสัมพันธ์กันระหว่าง2 ตาราง เพื่อให้ละการใส่ข้อมูล

          Syntax :

               CREATE TABLE table_name                     (column_name column_type,
                      FOREIGN KEY ( column_name) 
                      REFERENCES table_pk_name ( column_pk_name )                              [ ON UPDATE RESTRICT | CASCADE | SET NULL ]                              [ ON DELETE RESTRICT | CASCADE | SET NULL ] );

               ** จะต้องสร้างตารางที่ใช้ในการอ้างอิงก่อน จึงจะใช้  FOREIGN KEY  ได้

          Example :

            เช่น การสร้างเก็บข้อมูลนักศึกษา โดยมีรหัสนักศึกษา ชื่อ นามสกุล และภาควิชาที่ศึกษาอยู่ โดยในข้อมูลของ attribute ภาควิชานี้ ได้อ้างอิงไปยังตารางของ department ที่ได้สร้างไว้ก่อนหน้าในบทเรียนของ primary key ซึ่งตารางข้อมูลที่ได้ออกแบบไว้คือ

ภาพแสดง ตารางที่ได้ออกแบบไว้

ภาพแสดงตัวอย่างการสร้างตาราง

                         ทำการใส่ข้อมูลตามตารางที่ได้ออกแบบไว้ในขั้นต้น ผลที่ได้คือเมื่อใส่ข้อมูลที่ 1-3 สามารถใส่ได้ปกติ ไม่เกิดปัญหาใดๆ (เนื่องจากค่าที่ใส่เข้าไปไม่มีค่า id ที่ซ้ำกัน และค่า department ตรงกับค่า n_department ในตาราง department ที่ได้สร้างไว้ก่อนหน้า) แต่เมื่อทำการใส่ข้อมูลชุดที่ 4 ที่กำหนดให้ department เป็นค่าที่ไม่เคยมีมาก่อนในตารางอ้างอิง จึงเกิด error ขึ้น

ภาพแสดงการใส่ข้อมูล 3 ชุดแรก

ภาพแสดง error ที่เกิดขึ้นเมื่อใส่ข้อมูลชุดที่ 4

ทำการเรียกดูข้อมูล โดยเชื่อมทั้งสองตารางจะใช้คำสั่งใดนะ???
          เรียนรู้เพิ่มเติมคือหากต้องการเชื่อมทั้งสองตาราง จะใช้คำสั่ง JOIN หรือ INNER JOIN  โดยทั้งสองตัวมีความหมายเหมือนกัน สามารถแสดงได้ในรูปเชิงคณิตศาสตร์ในคือการ intersection กัน คือ นำส่วนที่เหมือนกันทับซ้อนกัน 

ภาพแสดง รูปแบบ intersection

          Syntax :

ภาพแสดง syntax ของการใช้ inner join หรือ join

        

          Example :
            ในตารางทั้งสองตารางด้านบน เราสร้างตาราง s_engineer ที่ข้อมูลของ department อ้างอิงไปยัง ข้อมูล n_department ของตาราง department เราจึงทำการเชื่อมสองตารางเข้าด้วยกัน และให้แสดงข้อมูลออกมา 

ภาพแสดง การแสดงข้อมูลของทั้งสองตาราง

            ตารางที่กำหนดให้แสดงค่าออกมา กำหนดให้แสดงค่าของตาราง s_engineer ทั้งหมด และให้แสดงข้อมูลในตาราง department เฉพาะข้อมูล location ที่ชื่อdepartment ตรงกันจึงได้ข้อมูลออกมาดังตารางด้านบน

อ้างอิงเว็บไซต์ที่เกี่ยวข้อง

1) http://www.w3schools.com/sql/sql_constraints.asp

2) http://na5cent.blogspot.com/2011/12/keys-database.html

3) http://code.function.in.th/sql/constraint

4) http://www.srisangworn.go.th/home/databaselearnx/ms3t1-1.htm
5) http://www.w3schools.com/sql/sql_join_inner.asp

DBMS : MySQL

DBMS

     DBMS มีหลายตัวให้เลือกใช้ แต่ที่ได้เลือกใช้ คือ mySQL เนื่องจากเป็น open source (คือ ตัว software เป็นแบบฟรีที่ถูกลิขสิทธิ์) และอีกสาเหตุที่เลือกใช้เนื่องจากมีอยู่ในเครื่องคอมพิวเตอร์อยู่แล้วโดยในตอนนั้นลงเป็น appserv ซึ่งจะมี php และ mySQL ติดมาด้วย จึงไม่ต้องทำการดาวน์โหลดใหม่

AppServ : Apache + PHP + MySQL

     เมื่อลงโปรแกรมเสร็จแล้ว ก่อนอื่นจะทำการ log in ก่อน เมื่อทำการ log in เสร็จแล้ว ฝั่งซ้ายมือของแต่ละบรรทัดจะขึ้นต้นด้วย mysql>
     ตัวแปรแกรมจะมีข้อมูลแถมมาให้ด้วย โดยเราสามารถเช็ค DB ได้จาก คำสั่ง SHOW DATABASE;

     ปล. คำสั่ง ตัวอักษรเล็กหรือใหญ่ ไม่ต่างกัน

ภาพแสดง การใช้คำสั่งในการดู DB

     ในภาพ จะเห็นได้ว่ามี DB ทั้งหมด 4 ตัว โดยหลังจากนี้ เราจะทำการเพิ่ม DB ของเราเอง โดยให้ชื่อว่า student ดังนั้นต้องใช้คำสั่งในการสร้าง DB นั่นคือ create database student; 

ภาพแสดง การใช้คำสั่ง ในการสร้าง DB

     หากเราต้องการลบ DB ตัวใด ก็จะใช้คำสั่ง drop database ตามด้วชื่อDB เช่น หากต้องการลบ thaicreate_2013 ก็จะใช้คำสั่ง drop database thaicreate_2013; 

ภาพแสดงตัวอย่างการลบ DB

     เมื่อทำการเช็ค DBทั้งหมด จะเห็นได้ว่าไม่มี thaicreate_2013 แล้ว และได้มี student เพิ่มเข้ามาแทน

ภาพแสดง DB หลังจากการเพิ่มและลบDB

      หากเราต้องการจะทำDB ตัวใด เราต้องทำการเลือก DB ก่อนถึงจะกำหนดชื่อตารางใน DB ได้ โดยใช้คำสั่ง use ตามด้วยชื่อ DB ที่สร้างไว้ เช่น กรณีนี้ ที่เราจะใช้ DB ของ student จึงใช้คำสั่ง use student;

   

ภาพแสดงการเลือก DB

     หากต้องการให้แสดงตารางทั้งหมดในDB นี้ จำใช้คำสั่ง show tables; แต่เนื่องจาก DB student เราพึ่งทำการสร้างใหม่ ทำให้ยังไม่มีตารางหรือข้อมูลใดๆเลย จึงได้แจ้งว่า Empty

ภาพแสดงการใช้คำสั่ง แสดงตาราง

     เราจะมาทำการสร้างตารางกัน โดยใช้คำสั่ง create table ตามด้วยชื่อตาราง(ข้อมูล1 ชนิด1,ข้อมูล2 ชนิด2) ;  เช่น เราอยากจะสร้างตารางที่ชื่อว่า ece โดยเก็บข้อมูล
1) ID student
2) Name
3) Surname
4) Major
     เราจะใช้คำสั่ง create table ece (id_student varchar(13), name varchar(20), surname varchar(20), major varchar(5));

ภาพแสดงการสร้างตารางและคอลัมน์ต่างๆในตาราง

     ข้อผิดพลาดที่พบ โดยในตอนแรกที่พบคือ ตอนสร้างคอลัมน์ในตาราง ตรง id_student ได้กำหนด type เป็น int เนื่องจากคิดว่า id ใส่ได้เพียงแค่ตัวเลขอยู่แล้ว แต่เมื่อทำการใส่ข้อมูลไปแล้ว ถึงพบว่า integer มีขีดจำกัดในตัวเลขต่ำสุดและสูงสุด คือ –2,147,483,648 ถึง 2,147,483,647 ทำให้ข้อมูลที่ใส่ไป กลับเป็นเลข integer ที่สูงสุด จึงได้ทำการแก้ไขเป็นการเก็บค่าโดยใช้ varchar 

     คำสั่งในการเปลี่ยนชื่อคอลัมน์ในตารางคือ 

          ALTER TABLE ece CHANGE id_student  id_student  varchar(13);

        อ้างอิงจาก : http://www.1keydata.com/sql/alter-table-rename-column.html

     หรืออาจทำหารลบตารางและสร้างใหม่ โดยการลบตารางใช้คำสั่ง

          drop table ece;

     ข้อมูลเกี่ยวกับ data type อ้างอิงจากเว็บไซต์ : 

                                        http://code.function.in.th/sqlserver/data-type

                                        http://www.tutorialspoint.com/sql/sql-data-types.htm

     หลังจากการเพิ่มตารางชื่อ ece แล้วทำการเช็คว่าได้เพิ่มแล้วจริงหรือไม่ โดยใช้คำสั่ง show tables; แต่ทีนี้จะไม่แจ้งว่า Empty แล้ว เนื่องจากได้เพิ่มไปแล้ว

ภาพแสดงการชื่อตาราง หลังจากมีเพิ่มตารางเข้าไป

      แต่ถ้าหากจะดูรายละเอียดต่างๆในตาราง เช่น ชื่อคอลัมน์ typeที่ใช้เก็บข้อมูล จะใช้คำสั่ง describe ตามด้วยชื่อตาราง เช่น describe ece; ก็จะเห็นข้อมูลต่างๆในตาราง ตามที่เราได้สร้างไว้ก่อนหน้า

ภาพแสดงการใช้คำสั่งdescribe

     การเพิ่มข้อมูลเข้าไปยังตาราง จะใช้คำสั่ง insert into ดังที่เคยได้ศึกษามาก่อนในเว็บ w3school 

ภาพแสดงตัวอย่าง การใส่ข้อมูลตัวที่ 1

ภาพแสดงตัวอย่าง การใส่ข้อมูลตัวที่ 2

     หากต้องการดูข้อมูลที่ใส่ไปทั้งหมด ในตาราง ece ทำได้โดยใช้คำสั่ง select * from ece; จะเห็นได้ว่ามีข้อมูลทั้งสองตัว ตามที่ได้เพิ่มเข้ามาก่อนหน้า

ภาพแสดงการแสดงข้อมูลทั้งหมดในตาราง

     หลังจากนั้นได้ทำการเพิ่มข้อมูลเข้าไปอีก 3 คน เป็น 5 คน โดยคนที่ 5 สร้างขึ้นเพื่อจะทดลองใช้คำสั่ง delete 

     

ภาพแสดงข้อมูลปัจจุบัน ที่มีจำนวน 5 คนก่อนทำการลบ

ภาพแสดง การลบ ข้อมูลตรงแถวที่ืname มีค่าเป็น love

ภาพแสดงข้อมูลหลังทำการลบ โดยเหลือข้อมูลอยู่ 4 ข้อมูล

     ทดลองทำการอัพเดตค่า โดยเลือกทำการอัพเดตชื่อของ "Noparat" และ "Phairojpattanakul" ให้เป็นตัวอักษรพิมพ์เล็กให้หมด ซึ่งทำได้ดังนี้

ภาพแสดงคำสั่งในการอัพเดตข้อมูลของ noparat

ภาพแสดงการตรวจสอบข้อมูลในตารางหลังการอัพเดต

     ทดลองให้เรียงข้อมูลที่นำมาแสดง ตามชื่อและนามสกุลของนักศึกษา โดยใช้คำสั่ง order by โดยมีรายละเอียดคำสั่งดังรูป

ภาพแสดงการจัดเรียงข้อมูล ตามชื่อและนามสกุลของนักศึกษา

     

อ้างอิงข้อมูลการทำจาก :

1) https://www.digitalocean.com/community/tutorials/a-basic-mysql-tutorial

2) http://www.1keydata.com/sql/alter-table-rename-column.html

3) http://www.w3schools.com/sql/

4) http://code.function.in.th/sqlserver/data-type

5) http://www.tutorialspoint.com/sql/sql-data-types.htm