คีย์หลักและคีย์ต่างประเทศเข้า SQLite ด้วยตัวอย่าง

หัวข้อด้านล่างนี้จะอธิบาย SQLite อธิบายข้อจำกัดโดยละเอียด เริ่มจาก PRIMARY KEY และ FOREIGN KEY ที่กำหนดและเชื่อมต่อตาราง และครอบคลุมกฎ NOT NULL, DEFAULT, UNIQUE และ CHECK ที่ใช้ตรวจสอบความถูกต้องของข้อมูลในแต่ละคอลัมน์

SQLite ข้อ จำกัด

ข้อจำกัดของคอลัมน์บังคับใช้กฎเกณฑ์กับค่าที่แทรกเข้าไปในคอลัมน์เพื่อตรวจสอบความถูกต้องของข้อมูล ข้อจำกัดเหล่านี้ถูกกำหนดไว้เมื่อสร้างตาราง ภายในคำจำกัดความของคอลัมน์ ข้อจำกัดเหล่านี้ช่วยให้ข้อมูลที่จัดเก็บมีความสอดคล้องและถูกต้องแม่นยำโดยการปฏิเสธค่าที่ฝ่าฝืนกฎที่คุณตั้งไว้ เช่น ค่าซ้ำ ค่าว่าง หรือค่าที่ไม่มีอยู่ในตารางที่เกี่ยวข้อง

SQLite คีย์หลัก

ค่าทั้งหมดในคอลัมน์คีย์หลักต้องไม่ซ้ำกันและต้องไม่ใช่ค่าว่าง คีย์หลักใช้ระบุแต่ละแถวในตารางได้อย่างเฉพาะเจาะจง

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

ไวยากรณ์:

มีหลายวิธีในการกำหนดคีย์หลักบนตาราง:

ในคำจำกัดความของคอลัมน์เอง:

ColumnName INTEGER NOT NULL PRIMARY KEY;

เป็นคำจำกัดความแยกต่างหาก:

PRIMARY KEY(ColumnName);

หากต้องการสร้างการรวมคอลัมน์เป็นคีย์หลัก:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite ข้อจำกัด NOT NULL, DEFAULT, UNIQUE และ CHECK

นอกจากคีย์หลักแล้ว SQLite มีข้อจำกัดหลายประการสำหรับคอลัมน์ที่ใช้ตรวจสอบความถูกต้องของค่าที่ป้อนลงในตาราง ข้อจำกัด NOT NULL, DEFAULT, UNIQUE และ CHECK แต่ละข้อถูกกำหนดไว้ในคำจำกัดความของคอลัมน์ และแต่ละข้อจะบังคับใช้กฎเฉพาะกับคอลัมน์นั้นๆ ประเภทข้อมูล และค่านิยม

ข้อจำกัด NOT NULL

การขอ SQLite ข้อจำกัด NOT NULL ป้องกันไม่ให้คอลัมน์มีค่าว่าง (null):

ColumnName INTEGER  NOT NULL;

ข้อจำกัดเริ่มต้น

กับ SQLite ข้อจำกัด DEFAULT คือ หากคุณไม่ได้ใส่ค่าใดๆ ในคอลัมน์ ระบบจะใส่ค่าเริ่มต้นแทน

ตัวอย่างเช่น:

ColumnName INTEGER DEFAULT 0;

หากคุณเขียนคำสั่งแทรกข้อมูลและไม่ได้ระบุค่าใดๆ สำหรับคอลัมน์นั้น คอลัมน์นั้นจะมีค่าเป็น 0

ข้อจำกัดเฉพาะตัว

การขอ SQLite ข้อจำกัด UNIQUE ป้องกันไม่ให้มีค่าซ้ำกันในคอลัมน์นั้น

ตัวอย่างเช่น:

EmployeeId INTEGER NOT NULL UNIQUE;

การตั้งค่านี้บังคับให้ค่า “EmployeeId” ต้องไม่ซ้ำกัน ไม่อนุญาตให้มีค่าซ้ำกัน โปรดทราบว่าข้อกำหนดนี้ใช้กับค่าในคอลัมน์ “EmployeeId” เท่านั้น

ตรวจสอบข้อจำกัด

การขอ SQLite ข้อจำกัด CHECK กำหนดเงื่อนไขเพื่อตรวจสอบค่าที่ป้อน หากค่าไม่ตรงกับเงื่อนไข ค่าจะไม่ถูกป้อน

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

คุณไม่สามารถใส่ค่าที่น้อยกว่า 10 ในช่อง "จำนวน" ได้

SQLite ต่างประเทศที่สำคัญ

การขอ SQLite คีย์ต่างประเทศ (Foreign Key) คือข้อจำกัดที่ตรวจสอบว่าค่าที่มีอยู่ในตารางหนึ่งนั้นมีอยู่ในอีกตารางหนึ่งที่มีความสัมพันธ์กับตารางแรกที่กำหนดคีย์ต่างประเทศนั้นหรือไม่

ในการทำงานกับหลายตาราง อาจมีบางกรณีที่สองตารางมีความสัมพันธ์กันผ่านคอลัมน์ร่วมกัน หากคุณต้องการให้แน่ใจว่าค่าที่ใส่ในตารางหนึ่งจะต้องมีอยู่ในคอลัมน์ของอีกตารางหนึ่ง คุณควรใช้ข้อจำกัดคีย์ต่างประเทศ (Foreign Key Constraint) กับคอลัมน์ร่วมกันนั้น

ในกรณีนี้ เมื่อคุณพยายามแทรกค่าลงในคอลัมน์นั้น คีย์ต่างประเทศจะตรวจสอบให้แน่ใจว่าค่าที่แทรกเข้าไปนั้นมีอยู่ในคอลัมน์ของตารางที่อ้างอิงอยู่แล้ว

โปรดทราบว่าข้อจำกัดคีย์ต่างประเทศไม่ได้เปิดใช้งานโดยค่าเริ่มต้นใน SQLiteคุณต้องเปิดใช้งานก่อนโดยเรียกใช้คำสั่งต่อไปนี้:

PRAGMA foreign_keys = ON;

มีการนำข้อจำกัดคีย์ต่างประเทศมาใช้ SQLite เริ่มตั้งแต่เวอร์ชัน 3.6.19

ตัวอย่างของการ SQLite ต่างประเทศที่สำคัญ

สมมติว่าเรามีตารางอยู่สองตาราง คือ ตารางนักเรียน และตารางแผนก

ตาราง Students มีรายชื่อนักเรียน และตาราง Departments มีรายชื่อภาควิชา นักเรียนแต่ละคนสังกัดภาควิชาใดภาควิชาหนึ่ง กล่าวคือ นักเรียนแต่ละคนจะมีคอลัมน์ departmentId

ต่อไปนี้เราจะมาดูกันว่าข้อจำกัดคีย์ต่างประเทศ (Foreign Key Constraint) มีประโยชน์อย่างไรในการทำให้มั่นใจว่าค่ารหัสแผนกในตารางนักเรียน (Students) จะต้องมีอยู่ในตารางแผนก (Departments) ด้วย

ดังนั้น หากเราสร้างข้อจำกัดคีย์ต่างประเทศบน DepartmentId ในตาราง Students แต่ละ DepartmentId ที่ถูกแทรกเข้าไปจะต้องมีอยู่ในตาราง Departments ด้วย

CREATE TABLE [Departments] (
	[DepartmentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[DepartmentName] NVARCHAR(50)  NULL
);
CREATE TABLE [Students] (
	[StudentId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
	[StudentName] NVARCHAR(50)  NULL,
	[DepartmentId] INTEGER  NOT NULL,
	[DateOfBirth] DATE  NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);

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

ในตัวอย่างนี้ ตาราง Departments มีความสัมพันธ์แบบคีย์นอกกับตาราง Students ดังนั้นค่า departmentId ใดๆ ที่แทรกเข้าไปในตาราง Students จะต้องมีอยู่ในตาราง Departments ด้วย หากคุณพยายามแทรกค่า departmentId ที่ไม่มีอยู่ในตาราง Departments ข้อจำกัดของคีย์นอกจะป้องกันไม่ให้คุณทำเช่นนั้น

ให้เราเพิ่มสองแผนก ได้แก่ “ไอที” และ “ศิลปะ” ลงในตารางแผนก โดยใช้ข้อมูลดังต่อไปนี้ คำสั่ง INSERT:

INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

คำสั่งทั้งสองนี้จะเพิ่มแผนกสองแผนกเข้าไปในตาราง Departments คุณสามารถตรวจสอบว่าค่าทั้งสองถูกเพิ่มเข้าไปแล้วหรือไม่โดยการเรียกใช้คำสั่ง “SELECT * FROM Departments” ในภายหลัง:

จากนั้นลองเพิ่มนักเรียนใหม่โดยใช้ departmentId ที่ไม่มีอยู่ในตาราง Departments:

INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);

ข้อมูลแถวจะไม่ถูกเพิ่ม และคุณจะได้รับข้อผิดพลาดว่า: ข้อจำกัด FOREIGN KEY ล้มเหลว

ความแตกต่างระหว่างคีย์หลักและคีย์รองใน SQLite

คีย์หลักและคีย์รองต่างก็ช่วยรักษาความถูกต้องของข้อมูล แต่มีบทบาทที่แตกต่างกัน คีย์หลักใช้ระบุแถวภายในตารางเดียว ในขณะที่คีย์รองเชื่อมโยงแถวระหว่างสองตารางที่เกี่ยวข้องกัน ตารางด้านล่างสรุปความแตกต่างหลักๆ ไว้

ในตัวอย่างเรื่องนักเรียนและภาควิชา DepartmentId เป็นคีย์หลักของตาราง Departments และเป็นคีย์นอกในตาราง Students ซึ่งเป็นตัวเชื่อมโยงนักเรียนแต่ละคนเข้ากับภาควิชาที่ถูกต้อง

SQLite คีย์หลักแบบผสม

คีย์หลักแบบผสม คือ คีย์หลักที่สร้างขึ้นจากสองคอลัมน์ขึ้นไป โดยใช้ในกรณีที่ไม่มีคอลัมน์ใดคอลัมน์หนึ่งมีค่าที่ไม่ซ้ำกัน แต่การรวมกันของคอลัมน์เหล่านั้นจะมีค่าที่ไม่ซ้ำกันสำหรับทุกแถว SQLite ถือว่าค่าที่รวมกันเป็นคีย์เดียว

ตัวอย่างเช่น ตารางการลงทะเบียนอาจอนุญาตให้นักเรียนคนเดียวกันลงทะเบียนในหลายวิชา และอนุญาตให้นักเรียนหลายคนลงทะเบียนในวิชาเดียวกันได้ แต่คู่ของนักเรียนและวิชาแต่ละคู่ควรปรากฏเพียงครั้งเดียวเท่านั้น:

CREATE TABLE Enrollments (
	StudentId INTEGER NOT NULL,
	CourseId INTEGER NOT NULL,
	Grade TEXT,
	PRIMARY KEY (StudentId, CourseId)
);

ในกรณีนี้ ทั้ง StudentId และ CourseId อาจไม่ซ้ำกันเมื่ออยู่เดี่ยวๆ แต่คู่ของ StudentId และ CourseId นั้นไม่ซ้ำกัน ดังนั้นนักเรียนคนเดียวกันจึงไม่สามารถลงทะเบียนเรียนในหลักสูตรเดียวกันได้สองครั้ง โปรดสังเกตประเด็นต่อไปนี้เมื่อคุณใช้คีย์แบบผสม:

• ใช้คีย์ผสมเมื่อคอลัมน์เดียวไม่สามารถระบุแถวได้อย่างเฉพาะเจาะจง
• คอลัมน์ทุกคอลัมน์ในคีย์ผสมต้องเป็นไปตามกฎของคีย์หลัก ดังนั้นค่าที่รวมกันจะต้องไม่ซ้ำกันและไม่ใช่ค่าว่าง
• คีย์ผสมจะถูกเขียนไว้ในส่วนของคำสั่ง PRIMARY KEY แยกต่างหากในระดับตาราง ไม่ใช่ภายในคำจำกัดความของคอลัมน์เดียว

SQLite การดำเนินการคีย์นอก: เมื่อลบ และ เมื่ออัปเดต

คีย์นอกยังสามารถควบคุมสิ่งที่เกิดขึ้นกับแถวลูกเมื่อแถวแม่ที่อ้างอิงถูกลบหรืออัปเดตได้อีกด้วย การกระทำที่เกี่ยวข้องกับการอ้างอิงเหล่านี้จะถูกเพิ่มเข้าไปในส่วนของคำสั่ง ON DELETE และ ON UPDATE เมื่อคุณกำหนดคีย์นอก SQLite สนับสนุนการดำเนินการห้าประการ:

• ไม่มีการดำเนินการ — การดำเนินการเริ่มต้น ซึ่งจะแสดงข้อผิดพลาดหากแถวย่อยยังคงอ้างอิงถึงแถวหลักอยู่
• จำกัด — ป้องกันการลบหรืออัปเดตทันที ก่อนที่การเปลี่ยนแปลงอื่นใดจะเกิดขึ้น
• ตั้งค่าเป็นโมฆะ — กำหนดค่าคอลัมน์คีย์ต่างประเทศของลูกให้เป็นค่าว่าง (null)
• ตั้งค่าเริ่มต้น — กำหนดค่าให้กับคอลัมน์คีย์นอกของลูกเป็นค่าเริ่มต้นที่ประกาศไว้
• น้ำตก — การเปลี่ยนแปลงนี้จะใช้หลักการเดียวกันกับแถวของลูก ดังนั้นการลบแถวหลักจะลบแถวของลูกไปด้วย

ตัวอย่างด้านล่างนี้สร้างตารางนักเรียนขึ้นใหม่ โดยที่การลบแผนกจะลบนักเรียนในแผนกนั้นโดยอัตโนมัติ และการอัปเดตรหัสแผนกจะอัปเดตนักเรียนที่ตรงกัน:

CREATE TABLE Students (
	StudentId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	StudentName NVARCHAR(50) NULL,
	DepartmentId INTEGER NOT NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

โปรดจำไว้ว่า การดำเนินการอ้างอิงจะทำงานก็ต่อเมื่อเปิดใช้งานการสนับสนุนคีย์ต่างประเทศเท่านั้น ดังนั้นให้รันคำสั่ง PRAGMA foreign_keys = ON ในตอนเริ่มต้นของการเชื่อมต่อทุกครั้ง หากไม่ทำเช่นนั้น... SQLite วิเคราะห์เงื่อนไข ON DELETE และ ON UPDATE แต่ไม่บังคับใช้เงื่อนไขเหล่านั้น

คำถามที่พบบ่อย