ON DELETE

Танилцуулга

Энэ бүлгийн өмнөх гурван хичээлд нэг үзэгдэл дахин дахин давтагдсан: заагдаж буй мөрийг устгах гэхэд FOREIGN KEY FOREIGN KEY constraint failed гэж хориглодог. Батыг устгах гэхэд түүний зээл, сурагчийг устгах гэхэд түүний бүртгэлүүд саад болсон. Энэ хамгаалалт өнчин мөр төрөхөөс сэргийлдэг тул маш үнэ цэнтэй. Гэвч бодит амьдралд гишүүн номын сангаас гардаг, сурагч бүртгэлээ цуцалдаг, зохиогчийн бүртгэл алдаатай үүсгэгдсэн байж болно. Ийм үед заалттай мөрүүдийг юу хийх вэ?

Энэ асуултад өгөгдлийн сан гурван өөр хариулт өгч чадна. Нэгдүгээрт: "хориглоно — эхлээд заалттай мөрүүдийг өөрөө шийд." Хоёрдугаарт: "заагдаж буй мөрийг устгавал заалттай мөрүүдийг хамт устга." Гуравдугаарт: "заагдаж буй мөрийг устгавал заалтыг хоосон болго, мөрийг нь үлдээ." Эдгээрийг RESTRICT, CASCADE, SET NULL гэж нэрлэдэг бөгөөд FOREIGN KEY зарлах үед ON DELETE гэсэн хэсгээр сонгоно.

Энэ хичээлд гурван хариулт тус бүрийг өгөгдлөөр харуулж, аль нь хэзээ зөв болохыг ярилцана. Хамгийн чухал нь — гурвын дотроос хамгийн тухтай мэт санагддаг CASCADE нь хамгийн аюултай нь гэдгийг өөрийн нүдээр харна. Хичээлийн төгсгөлд таны ашиглаж буй энэ платформ устгалыг хэрхэн болгоомжтой зохицуулдгийг бодит жишээ болгон үзнэ.

Зүйрлэл

Компанийн ажилтны бүртгэлийг төсөөлөөд үзээрэй. Ажилтан бүр хэлтэс рүү, хэлтэс бүр компани руу заадаг. Нэг өдөр нэг хэлтэс татан буугдлаа. Хүний нөөцийн албанд гурван өөр журам байж болно. Эхний журам: "Хэлтэст ажилтан байгаа бол татан буулгахыг хориглоно — эхлээд ажилтнуудыг өөр хэлтэс рүү шилжүүл эсвэл чөлөөл." Хоёр дахь журам: "Хэлтсийг татан буулгавал түүний ажилтнуудыг автоматаар чөлөөл." Гурав дахь журам: "Хэлтсийг татан буулгавал ажилтнуудыг үлдээ, гэхдээ хэлтсийн нүдийг нь хоосон болго — тэд шинэ хэлтэс хүлээж байна."

Аль журам зөв бэ? Энэ нь байгууллагаас хамаарна — нэг байгууллагад "автоматаар чөлөөл" гэдэг нь хүнлэг бус алдаа, нөгөөд нь хэвийн дүрэм. Өгөгдлийн сан ч мөн адил: ON DELETE нь техникийн тохиргоо гэхээсээ илүү бизнесийн шийдвэр юм. Өгөгдлийн сан таны сонгосон журмыг үнэнчээр, хурдан, нэг ч удаа мартахгүйгээр гүйцэтгэнэ — гэвч зөв журмыг сонгох нь таны үүрэг.

Эхлэхээс өмнө

Энэ хичээлийн бүх жишээ FOREIGN KEY-ийн шалгалт асаалттай байхыг шаарддаг. Хадгалаагүй өөрчлөлт байхгүй үед Edit Pragmas табд Foreign Keys тэмдэглэгдсэн эсэхийг дахин шалгаарай (Бүлэг 5, Хичээл 1). Энэ тохиргоо унтраалттай бол SQLite зөвхөн шалгалтыг бус, ON DELETE-ийн бүх үйлдлийг үл тоомсорлодог — CASCADE ч ажиллахгүй, SET NULL ч ажиллахгүй. Мөн энэ хичээлийн турш Write Changes дарахгүй.

Анхдагч зан: NO ACTION

Бүлэг 5-ийн эхний хичээлд бид ON DELETE огт бичээгүй. Тэр үед SQLite анхдагч журмаа хэрэглэсэн — NO ACTION, өөрөөр хэлбэл "өөрөө юу ч хийхгүй, харин өнчин мөр үүсэх гэвэл хориглоно." Энэ нь RESTRICT-тэй бараг адилхан бөгөөд ялгаа нь зөвхөн шалгалт хийгдэх нарийн мөчид оршдог — энэ сургалтын хүрээнд хоёулаа ижил үр дүн өгнө. Гэхдээ RESTRICT-ийг ил тод бичих нь query уншиж буй хүнд "энэ хориг санаатай" гэдгийг харуулдаг тул бид түүнийг доор ашиглана.

Журам 1: RESTRICT — хориглох

Номын сангийн зээлийн түүх маш чухал: аль ном хэдэн удаа зээлэгдсэн, хэн хугацаа хэтрүүлсэн гэх мэт. Зээлийн түүхтэй номыг санамсаргүй устгах нь тэр түүхийг эвдэнэ. Тиймээс энд хатуу хориг тавина. Query:

sql

CREATE TABLE loans (
    id INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL,
    member_id INTEGER NOT NULL,
    loaned_on TEXT NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books (id) ON DELETE RESTRICT,
    FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE RESTRICT
);

INSERT INTO loans (book_id, member_id, loaned_on)
VALUES (4, 1, '2026-11-03');

Энэ тушаал юу гэж хэлж байна вэ гэвэл: “loans хүснэгт үүсгэ; book_id нь books руу, member_id нь members руу заах бөгөөд заагдаж буй ном эсвэл гишүүнийг устгахыг хориглоно; дараа нь Бат Clean Code-ийг зээлсэн гэж нэм.” ON DELETE-ийн хэсэг REFERENCES-ийн яг араас, FOREIGN KEY-ийн мөрийн төгсгөлд бичигддэг.

Одоо Clean Code-ийг устгах гэж оролдъё:

sql

DELETE FROM books WHERE id = 4;

Үр дүн:

FOREIGN KEY constraint failed

Энэ мессеж юу гэж хэлж байна вэ гэвэл: "гадаад түлхүүрийн дүрэм зөрчигдлөө" — loans хүснэгтэд энэ ном руу заасан мөр байгаа тул устгахыг хориглосон. Бүлэг 5-ийн эхний хичээлтэй яг ижил үр дүн, гэвч одоо энэ нь санамсаргүй анхдагч биш, харин ил тод бичигдсэн шийдвэр. Хэрэв номыг үнэхээр устгах шаардлагатай бол эхлээд түүний зээлүүдийг юу хийхээ хүн өөрөө шийдэх ёстой — архивлах уу, устгах уу. RESTRICT нь тэр шийдвэрийг өгөгдлийн сан биш, хүн гаргахыг шаарддаг.

Журам 2: CASCADE — хамт устгах

Одоо сурагчид ба элсэлтийн жишээ рүү буцъя. Энэ удаад бид "хэрэв сурагчийн бүртгэл устгагдвал түүний элсэлтүүд утгагүй болно" гэж шийдлээ гэж бодъё — жишээ нь туршилтаар үүсгэсэн бүртгэлүүдийг цэвэрлэх үед. Query:

sql

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on TEXT NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE
);

INSERT INTO students (name) VALUES ('Бат'), ('Сараа');
INSERT INTO courses (title) VALUES ('Python анхан шат'), ('FastAPI'), ('SQL');
INSERT INTO enrollments (student_id, course_id, enrolled_on)
VALUES
    (1, 1, '2026-01-10'),
    (1, 2, '2026-03-02'),
    (1, 3, '2026-09-15'),
    (2, 1, '2026-02-05'),
    (2, 3, '2026-09-20');

Энэ тушаал юу гэж хэлж байна вэ гэвэл: "өмнөх хичээлийн хүснэгтүүдийг үүсгэ, гэхдээ энэ удаад сурагч эсвэл сургалт устгагдвал холбогдох элсэлтүүдийг автоматаар хамт устгадаг болго; дараа нь хоёр сурагч, гурван сургалт, таван элсэлт нэм."

enrollments хүснэгт устгалаас өмнө:

student_id | course_id | enrolled_on
-----------+-----------+-----------
1          | 1         | 2026-01-10
1          | 2         | 2026-03-02
1          | 3         | 2026-09-15
2          | 1         | 2026-02-05
2          | 3         | 2026-09-20

Одоо Батыг устгая:

sql

DELETE FROM students WHERE id = 1;

Мессеж: 1 rows affected. enrollments хүснэгт дараа нь:

student_id | course_id | enrolled_on
-----------+-----------+-----------
2          | 1         | 2026-02-05
2          | 3         | 2026-09-20

Энэ query юу гэж хэлж байна вэ гэвэл: “students хүснэгтээс id нь 1 байгаа сурагчийг устга.” Бид зөвхөн нэг сурагчийг устгахыг хүссэн ч Батын гурван элсэлт ч хамт алга болжээ — CASCADE (шаталсан урсгал, хүрхрээ гэсэн утгатай үг) журам устгалыг заалтын дагуу доош урсгасан. Сараагийн хоёр элсэлт хөндөгдөөгүй. Өнчин мөр үлдээгүй, хориг ч гараагүй.

Одоо энэ хичээлийн хамгийн чухал ажиглалтыг анхаар: мессеж 1 rows affected гэж хэлсэн. Бодит байдал дээр дөрвөн мөр устсан — нэг сурагч, гурван элсэлт. SQLite-ийн “rows affected” тоолуур зөвхөн таны шууд тушаасан мөрүүдийг тоолдог, CASCADE-ээр хамт устсан мөрүүдийг тоолдоггүй. Бүлэг 3-т бид “rows affected” бол WHERE-гүй DELETE-ийг илрүүлэх цорын ганц дохио гэж сурсан — CASCADE тэр дохиог чимээгүй болгодог. Хэрэв элсэлтүүд өөрсдөө өөр хүснэгтүүдээр заагдаж, тэдгээр нь ч CASCADE-тэй байсан бол нэг DELETE хэдэн арван хүснэгтээр урсаж, мянга мянган мөр устгаж болох бөгөөд мессеж тайвнаар “1 rows affected” гэсээр байх болно.

Тиймээс CASCADE-тэй хүснэгтэд Бүлэг 3-ын "эхлээд SELECT" дадлыг өргөтгөх хэрэгтэй: зөвхөн устгах мөрөө бус, түүнтэй хамт урсах мөрүүдийг ч шалга. Батыг устгахаас өмнө SELECT COUNT(*) FROM enrollments WHERE student_id = 1; гэж ажиллуулсан бол “3” гэсэн хариу авч, бүх үр дагаврыг урьдчилан мэдэх байсан.

Журам 3: SET NULL — заалтыг хоосон болгох

Гурав дахь журмыг зохиогчид ба бүтээлүүдийн жишээгээр харъя. Хэрэв зохиогчийн бүртгэлийг (жишээ нь алдаатай үүсгэснээс болж) устгавал түүний номнууд номын сангаас алга болох ёсгүй — ном тавиур дээрээ байсаар байна. Зөвхөн "зохиогч хэн бэ" гэдэг мэдээлэл түр хугацаанд мэдэгдэхгүй болно. Query:

sql

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE works (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors (id) ON DELETE SET NULL
);

INSERT INTO authors (name) VALUES ('Д.Нацагдорж'), ('Robert C. Martin');
INSERT INTO works (title, author_id)
VALUES
    ('Учиртай гурван толгой', 1),
    ('Clean Code', 2),
    ('The Clean Coder', 2),
    ('Clean Architecture', 2);

Энэ тушаал юу гэж хэлж байна вэ гэвэл: “authors болон works хүснэгт үүсгэ; works-ийн author_id нь хоосон байж болох бөгөөд зохиогч устгагдвал хоосон болно; хоёр зохиогч, дөрвөн бүтээл нэм.”

works хүснэгт устгалаас өмнө:

id | title                 | author_id
---+-----------------------+----------
1  | Учиртай гурван толгой | 1
2  | Clean Code            | 2
3  | The Clean Coder       | 2
4  | Clean Architecture    | 2

Одоо Robert C. Martin-ий бүртгэлийг устгая:

sql

DELETE FROM authors WHERE id = 2;

Мессеж: 1 rows affected. works хүснэгт дараа нь:

id | title                 | author_id
---+-----------------------+----------
1  | Учиртай гурван толгой | 1
2  | Clean Code            | NULL
3  | The Clean Coder       | NULL
4  | Clean Architecture    | NULL

Энэ query юу гэж хэлж байна вэ гэвэл: “authors хүснэгтээс id нь 2 байгаа зохиогчийг устга.” Зохиогч алга болсон ч түүний гурван ном хүснэгтэд үлдэж, author_id нь NULL болжээ — "зохиогч нь одоогоор мэдэгдэхгүй." Өнчин мөр үүссэнгүй: NULL заалт хаашаа ч заадаггүй тул FOREIGN KEY-ийн дүрмийг зөрчихгүй. Хэрэв дараа нь зохиогчийг зөв мэдээллээр дахин бүртгэвэл UPDATE-ээр номнуудыг түүн рүү дахин холбож болно. Энд ч мөн “1 rows affected” нь гурван номын өөрчлөлтийг тоолоогүйг анзаараарай.

SET NULL-ийн нэг нөхцөл бий: заалтын багана NULL зөвшөөрөх ёстой. Хэрэв бид author_id INTEGER NOT NULL гэж зарласан бол юу болохыг доор Түгээмэл алдаанууд хэсэгт харна.

Аль журмыг хэзээ сонгох вэ

Гурван журмыг харсны дараа сонголтын зарчмыг нэгтгэе. Асуух гол асуулт: "заагдаж буй мөр алга болбол заалттай мөр ямар утгатай хэвээр үлдэх вэ?"

RESTRICT — заалттай мөр бие даасан, чухал түүх бол. Зээлийн түүх, санхүүгийн гүйлгээ, гэрчилгээ гэх мэт. Тэдгээрийг автоматаар устгах нь мэдээлэл алдах, автоматаар хоосон болгох нь түүхийг гажуудуулах болно. Хүн өөрөө шийдвэр гаргах ёстой. Эргэлзвэл RESTRICT сонго — энэ нь хамгийн аюулгүй бөгөөд хэрэв хэт хатуу санагдвал дараа нь сулруулж болно.

CASCADE — заалттай мөр эцэг эхгүйгээр огт утгагүй, эцэг эхийнхээ нэг хэсэг бол. Захиалгын доторх бүтээгдэхүүний мөрүүд захиалгагүйгээр утгагүй. Устгагдсан туршилтын бүртгэлийн элсэлтүүд утгагүй. CASCADE-ийг сонгох бүрдээ "хэрэв хэн нэгэн санамсаргүй эцэг эхийг устгавал хүүхдүүд нь бүгд алга болоход би сэтгэл хангалуун байх уу" гэж асуу.

SET NULL — заалттай мөр бие даан оршиж чадах бөгөөд холбоос нь сонголттой бол. Зохиогч нь тодорхойгүй ном, менежер нь ажлаас гарсан ажилтан, ангилал нь устгагдсан бүтээгдэхүүн. Мөр хэвээр, зөвхөн холбоос түр тасарна.

Нэмэлт мэдээлэл болгон: SQLite-д SET DEFAULT гэсэн дөрөв дэх журам (заалтыг анхдагч утга руу шилжүүлэх) байдаг ч ховор хэрэглэгддэг. Мөн ON UPDATE гэсэн ижил бүтэцтэй хэсэг бий бөгөөд энэ нь заагдаж буй мөрийн PRIMARY KEY өөрчлөгдөх үед юу хийхийг тодорхойлдог. Бүлэг 4-т “PRIMARY KEY-ийг хэзээ ч бүү өөрчил” гэж сурсан тул ON UPDATE-ийг тохируулах шаардлага бараг гардаггүй.

Бодит жишээ: энэ платформ устгалыг хэрхэн зохицуулдаг вэ

Таны уншиж буй энэ сургалтын платформын өгөгдлийн сан олон давхар заалттай: байгууллага → хэрэглэгчид ба сургалтууд → бүлгүүд → хичээлүүд, мөн хэрэглэгч бүр элсэлт, хичээлийн ахиц, гэрчилгээ, сэтгэгдэлтэй. Хэрэглэгчийн бүртгэлийг бүрмөсөн устгах нь түүний элсэлт, ахиц, гэрчилгээг бүгдийг арилгана гэсэн үг бөгөөд энэ нь буцаах боломжгүй. Тиймээс платформ энэ үйлдлийг хоёр хамгаалалтаар хүрээлдэг. Нэгдүгээрт, устгалыг өгөгдлийн сангийн CASCADE-д чимээгүй даатгахын оронд програмын код заалтуудын модыг доош нь дагаж, хүүхдүүдийг эхэлж, эцэг эхийг сүүлд нь, тодорхой дарааллаар устгадаг — Бүлэг 5-ийн эхний хичээлийн "хүүхдийг эхэлж" дүрэм яг энд амьдарч байна. Хоёрдугаарт, байнгын устгал болон идэвхгүй болгох (Бүлэг 3-ын soft delete) хоёр тусдаа, ил тод шошготой товчоор хийгддэг бөгөөд байнгын устгал баталгаажуулалт асуудаг, мөн зөвхөн хамгийн дээд эрхтэй админд л зөвшөөрөгддөг. Энэ хичээлийн сургамжийг бодит систем ингэж хэрэгжүүлдэг: устгал хэдий чинээ өргөн урсах тусам түүнийг төдий чинээ ил тод, удаан, болгоомжтой болгох.

Дасгалын хүснэгтүүдийг цэвэрлэх

Бид Write Changes дараагүй тул Revert Changes дарахад энэ хичээлийн бүх хүснэгт (loans, students, courses, enrollments, authors, works) алга болно. Database Structure табд зөвхөн books, members үлдсэн эсэхийг шалгаарай.

Туршаад үзээрэй

Хэрэв сонирхож байвал CASCADE-ийн гинжийг өөрийн нүдээр харж үзээрэй: courses → sections → lessons гэсэн гурван хүснэгт үүсгэж, хоёр заалтад хоёуланд нь ON DELETE CASCADE тавиад, нэг сургалтад хоёр бүлэг, бүлэг бүрт гурван хичээл нэмнэ. Дараа нь сургалтыг устгаад мессежийн “rows affected”-ийг хараад, lessons хүснэгтэд юу үлдсэнийг шалгаарай. Мөн ижил бүтцийг RESTRICT-ээр дахин үүсгэж, ижил устгалыг оролдож харьцуулж болно. Дуусаад Revert Changes дарна. Эдгээр нь зөвхөн санал бөгөөд дараагийн хичээлүүд тэдгээрийг хийсэн гэж тооцохгүй.

Түгээмэл алдаанууд

NOT NULL баганад SET NULL тавих. Хэрэв author_id INTEGER NOT NULL гэж зарлаад ON DELETE SET NULL нэмбэл SQLite хүснэгтийг алдаагүй үүсгэнэ. Алдаа нь зохиогч устгах үед л гарна: NOT NULL constraint failed: works.author_id. Энэ мессеж юу гэж хэлж байна вэ гэвэл: “works хүснэгтийн author_id баганад хоосон утга хориглосон дүрэм зөрчигдлөө.” SET NULL журам заалтыг хоосон болгох гэж оролдсон, гэтэл NOT NULL хориглосон — хоёр дүрэм зөрчилдөж, устгал бүтэлгүйтсэн. SET NULL сонговол заалтын баганад NOT NULL бүү тавь.

FOREIGN KEY-ийн шалгалт унтраалттай байх. Энэ тохиргоо унтраалттай бол CASCADE хүүхдүүдийг устгахгүй, SET NULL заалтыг хоосон болгохгүй, RESTRICT хориглохгүй — гурвуулаа чимээгүй ажиллахаа болино. Үр дүнд нь эцэг эх устаж, хүүхдүүд өнчин үлдэнэ. Python-оос SQLite ашиглах үед холболт бүрт PRAGMA foreign_keys = ON гэж ил тод хэлэхээ мартвал яг ийм зүйл болно.

CASCADE-ийн хэмжээг “rows affected”-ээр үнэлэх. Журам 2-т харсанчлан тоолуур зөвхөн шууд устгасан мөрүүдийг тоолдог. CASCADE-тэй хүснэгтэд том устгал хийхээсээ өмнө хамааралтай хүснэгт бүрт COUNT-оор хэдэн мөр урсахыг шалгаарай, мөн Бүлэг 3-ын DELETE хичээлийн backup дадлыг санаарай.

Түлхүүр үгийг буруу бичих. ON DELETE CASCADES гэж илүү үсэгтэй бичвэл near "CASCADES": syntax error гарна. Энэ мессеж юу гэж хэлж байна вэ гэвэл: “CASCADES гэдэг үгийн ойролцоо дүрэм зөрчигдлөө.” Зөв хэлбэрүүд: RESTRICT, CASCADE, SET NULL, SET DEFAULT, NO ACTION.

Байгаа хүснэгтэд ON DELETE нэмэх гэж оролдох. Бүлэг 4-ийн ALTER хичээлд сурсанчлан SQLite байгаа баганын constraint-ыг өөрчлөх боломжгүй. ON DELETE журмыг өөрчлөх шаардлага гарвал хүснэгтийг дахин бүтээх дөрвөн алхмыг хийх хэрэгтэй. Тиймээс FOREIGN KEY бүрийг зарлах үед журмыг анхнаас нь бодох нь хамгийн хялбар.

Товч дүгнэлт

Энэ хичээлд та заагдаж буй мөрийг устгах үеийн гурван журмыг сурлаа. RESTRICT (мөн анхдагч NO ACTION) нь заалттай мөр байвал устгалыг хориглож, шийдвэрийг хүнд үлдээдэг — чухал түүхэн бичилтэд тохиромжтой. CASCADE нь устгалыг заалтын дагуу доош урсгаж, хүүхдүүдийг хамт устгадаг — эцэг эхийнхээ нэг хэсэг болох мөрүүдэд тохиромжтой боловч “rows affected” тоолуурыг чимээгүй болгодог тул хамгийн аюултай. SET NULL нь заалтыг хоосон болгож мөрийг үлдээдэг — бие даан оршиж чадах мөрүүдэд тохиромжтой, гэхдээ заалтын багана NULL зөвшөөрөх ёстой. ON DELETE бол техникийн бус, бизнесийн шийдвэр бөгөөд бодит системүүд өргөн урсдаг устгалыг ил тод, баталгаажуулалттай, эрхээр хязгаарлагдсан үйлдэл болгодог.

Ингээд Бүлэг 5 дууслаа. Та одоо хүснэгтүүдийг нэг-олон болон олон-олон харилцаагаар холбож, тэдгээрийн устгалын журмыг сонгож чадна. Дараагийн бүлэгт бид хүснэгт зохион байгуулалтын хамгийн үндсэн асуултад хариулна: яагаад мэдээллийг олон жижиг хүснэгтэд хуваах ёстой вэ, нэг том хүснэгтэд бүгдийг хийвэл юу болох вэ? Бид нэгэн "бүгдийг нэг дор" хүснэгтийг бүтээж, түүнийг гурван өөр аргаар эвдэрч байгааг түүх болгон харна.