ป้ายกำกับ: Information Schema

ในฐานะนักพัฒนาระบบหรือ Database Architect การทำความเข้าใจโครงสร้างความสัมพันธ์ของข้อมูลเป็นสิ่งสำคัญอย่างยิ่ง การรู้ว่าตารางใดถูกกำหนดให้มี Primary Key (PK), Foreign Key (FK), หรือ Unique Constraint (UQ) จะช่วยให้เราสามารถตรวจสอบความสมบูรณ์ของข้อมูล (Data Integrity) และออกแบบการ Query ที่มีประสิทธิภาพได้อย่างถูกต้อง

บทความนี้จะแนะนำวิธีการใช้ PostgreSQL เพื่อดึงข้อมูลสถานะของ Constraint ทั้งหมดในทุกตารางของฐานข้อมูล โดยใช้ประโยชน์จากระบบ Metadata ของ PostgreSQL คือ information_schema

⚙️ หลักการทำงาน: การตรวจสอบ Constraints ใน PostgreSQL

PostgreSQL จัดเก็บข้อมูลเกี่ยวกับโครงสร้างของฐานข้อมูลไว้ใน information_schema ซึ่งเป็นมุมมอง (View) ที่ให้ข้อมูลเกี่ยวกับตาราง คอลัมน์ และ Constraints ต่างๆ การเขียน Query เพื่อดึงข้อมูลนี้จึงเป็นการ Join ข้อมูลระหว่างตารางหลัก (information_schema.tables) กับตาราง Constraints (information_schema.table_constraints) เพื่อกรองเฉพาะประเภท Constraint ที่เราสนใจ (PK, FK, UQ) เท่านั้น

💡 PostgreSQL Query สำหรับตรวจสอบ PK, FK, UQ

โค้ด SQL ด้านล่างนี้คือ Script ที่สมบูรณ์แบบสำหรับตรวจสอบสถานะของ Constraint ในทุกตารางที่อยู่ใน Schema ที่กำหนด:

SQL

SELECT
    t.table_schema,
    t.table_name,
    -- ตรวจสอบว่ามี Primary Key หรือไม่
    MAX(CASE WHEN tc.constraint_type = 'PRIMARY KEY' THEN 'YES' ELSE 'NO' END) AS Has_PK,
    -- ตรวจสอบว่ามี Foreign Key หรือไม่
    MAX(CASE WHEN tc.constraint_type = 'FOREIGN KEY' THEN 'YES' ELSE 'NO' END) AS Has_FK,
    -- ตรวจสอบว่ามี Unique Constraint หรือไม่
    MAX(CASE WHEN tc.constraint_type = 'UNIQUE' THEN 'YES' ELSE 'NO' END) AS Has_UQ,
    -- สรุปสถานะรวม
    CASE
        WHEN COUNT(tc.constraint_type) = 0 THEN 'NO CONSTRAINTS' 
        ELSE 'CONSTRAINED' 
    END AS Constraint_Status
FROM
    information_schema.tables AS t
LEFT JOIN
    information_schema.table_constraints AS tc
    ON t.table_schema = tc.table_schema
    AND t.table_name = tc.table_name
    AND tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE') -- กรองเฉพาะ 3 ประเภทที่สนใจ
WHERE
    t.table_type = 'BASE TABLE' -- กรองเอาเฉพาะตารางจริง
GROUP BY
    t.table_schema, t.table_name
ORDER BY
    t.table_schema, t.table_name;

📝 คำอธิบายองค์ประกอบของ Query

• information_schema.tables (t): ใช้เพื่อดึงรายชื่อตารางทั้งหมดในฐานข้อมูล
• information_schema.table_constraints (tc): ใช้เพื่อดึงข้อมูลประเภทของ Constraint ที่ถูกกำหนดไว้
• MAX(CASE WHEN ...): เป็นการใช้ฟังก์ชัน Aggregate เพื่อตรวจสอบว่าในกลุ่มของตารางนั้นๆ มี Constraint ประเภทที่กำหนดหรือไม่ (ถ้ามีจะแสดง ‘YES’)
• GROUP BY: จัดกลุ่มผลลัพธ์ตาม Schema และชื่อตาราง เพื่อให้ได้สถานะของ Constraint สำหรับตารางแต่ละตาราง

✅ บทสรุปและข้อแนะนำในการใช้งาน

การรัน Query นี้เป็นเครื่องมือที่ทรงพลังอย่างยิ่งในการทำ Database Auditing หรือการตรวจสอบความถูกต้องของโครงสร้างฐานข้อมูลก่อนการ Deployment หรือการแก้ไข Schema ครั้งใหญ่

หากผลลัพธ์แสดงว่าตารางใดมี Has_PK เป็น ‘NO’ ทั้งที่ควรจะมี อาจหมายถึงการออกแบบฐานข้อมูลที่บกพร่อง ซึ่งอาจนำไปสู่ปัญหาความซ้ำซ้อนของข้อมูลในอนาคต การทำความเข้าใจ Constraint เหล่านี้จึงเป็นพื้นฐานสำคัญสำหรับ System Specialist ทุกท่าน