Нэг-олон харилцаа

Танилцуулга

Өмнөх хичээлд FOREIGN KEY-ийг сурахдаа зээлийн хүснэгт үүсгэсэн. Тэнд зээл бүр нэг ном, нэг гишүүн рүү заасан бөгөөд бид FOREIGN KEY-г хаана тавих талаар огт эргэлзээгүй — зээлийн хүснэгт л ганц боломжит газар байсан. Харин одоо хоёр хүснэгтийг шууд холбох нөхцөл рүү шилжье: зохиогчид ба номнууд. Энд сонголт бий мэт харагддаг: зохиогчийн хүснэгтэд "энэ зохиогчийн ном" гэсэн заалт тавих уу, эсвэл номын хүснэгтэд "энэ номын зохиогч" гэсэн заалт тавих уу?

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

Энэ хэлбэрийг нэг-олон харилцаа (one-to-many) гэдэг бөгөөд өгөгдлийн санд хамгийн түгээмэл харилцаа юм. Нэг зохиогч олон ном, нэг сургалт олон бүлэг, нэг бүлэг олон хичээл, нэг гишүүн олон зээл — эдгээр бүгд ижил хэлбэртэй. Нэг удаа зөв ойлговол та бодит системүүдийн хүснэгтүүдийн тал хувийг нь уншиж чадна.

Зүйрлэл

Сургуулийн ангийн жагсаалтыг төсөөлөөд үзээрэй. Нэг анги олон сурагчтай, харин сурагч бүр яг нэг ангид суралцдаг. Хэрэв та ангийн бүртгэлийн хуудсанд "энэ ангийн сурагчид" гэсэн нүд гаргавал хэдэн нүд хэрэгтэй вэ? Нэг анги 20 сурагчтай, нөгөө нь 35 — хуудасны хэлбэрийг тогтоох боломжгүй. Харин сурагч бүрийн хуудсанд "Анги: 5А" гэсэн ганц нүд гаргавал бүх зүйл цэгцтэй: сурагч бүр яг нэг утга бичнэ.

Энэ зүйрлэлд бүх хичээлийн санаа багтаж байна. Заалтыг "олон" талд тавина — сурагчийн хуудсанд, учир нь сурагч бүр яг нэг анги руу заадаг. "Нэг" тал (анги) өөрийн сурагчдыг жагсаах шаардлагагүй: хэрэв “5А-д хэн хэн суралцдаг вэ” гэж асуувал бүх сурагчийн хуудаснаас "Анги: 5А" гэснийг шүүж олно. Өгөгдлийн санд FOREIGN KEY яг ингэж ажилладаг.

Эхлэх өгөгдөл: зохиогчид

library.db-г нээгээд зохиогчдын хүснэгт үүсгэе (энэ хичээлийн турш Write Changes дарахгүй):

sql

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

INSERT INTO authors (name)
VALUES ('Д.Нацагдорж'), ('Ч.Лодойдамба'), ('Robert C. Martin'),
       ('Eric Matthes'), ('Al Sweigart');

authors хүснэгт:

id | name
---+-----------------
1  | Д.Нацагдорж
2  | Ч.Лодойдамба
3  | Robert C. Martin
4  | Eric Matthes
5  | Al Sweigart

Энэ тушаал юу гэж хэлж байна вэ гэвэл: “authors хүснэгт үүсгэж, таван зохиогч нэм.” Бидэнд дараах бүтээлүүдийг холбох хэрэгтэй: Нацагдоржийн "Учиртай гурван толгой" (1934) болон "Миний нутаг" (1933); Лодойдамбын "Тунгалаг Тамир" (1962); Robert C. Martin-ий “Clean Code” (2008), “The Clean Coder” (2011), “Clean Architecture” (2017); Eric Matthes-ийн “Python Crash Course” (2015); мөн зохиогч нь тодорхойгүй "Монголын нууц товчоо" (1240). Al Sweigart-ын номыг номын сан хараахан аваагүй. Зарим зохиогч нэг, зарим нь олон, нэг нь нэг ч номгүй, нэг ном зохиогчгүй — бодит амьдрал ийм л холимог байдаг.

Буруу хувилбар 1: заалтыг "нэг" талд тавих

Анх бодоход зохиогчийн хүснэгтэд "номын дугаар" гэсэн багана нэмэх нь байгалийн санагдаж магадгүй. Ном бүрийг тусдаа хүснэгтэд (номын дугаартай) байна гэж үзээд, зохиогчдын хүснэгтэд book_id нэмье. Нацагдорж, Лодойдамба, Matthes-ийн хувьд асуудал алга мэт. Гэвч Robert C. Martin гурван номтой — нэг нүдэнд гурван дугаар багтахгүй. Цорын ганц арга нь Martin-ийг гурван удаа бичих:

authors (буруу хувилбар)
id | name             | book_id
---+------------------+--------
1  | Д.Нацагдорж      | 1
2  | Д.Нацагдорж      | 2
3  | Ч.Лодойдамба     | 3
4  | Robert C. Martin | 4
5  | Robert C. Martin | 5
6  | Robert C. Martin | 6
7  | Eric Matthes     | 7
8  | Al Sweigart      | NULL

Энэ хүснэгтийг хараад асуудлуудыг тоолъё. Нацагдорж хоёр мөр, Martin гурван мөр эзэлж, тус бүр өөр өөр id-тай болсон — өөрөөр хэлбэл зохиогч бүр нэг "хэн болох" дугаартай байх ёстой байтал Martin гурван өөр хүн шиг харагдаж байна. Хэрэв бид Martin-ий нэрийг “Robert Cecil Martin” болгон засах гэвэл гурван мөрийг бүгдийг нь засах ёстой бөгөөд нэгийг нь мартвал хоёр өөр нэр зэрэгцэн оршино. "Номын санд хэдэн зохиогч байна вэ" гэж SELECT COUNT(*) хийвэл 5 биш, 8 гэсэн худал хариу гарна. Энэ хувилбар нь мэдээллийг давхардуулж байгаа бөгөөд давхардал нь алдааны үүр юм — дараагийн бүлэгт энэ тухай бүтэн хичээл бий.

Буруу хувилбар 2: жагсаалтыг нэг нүдэнд бичих

Давхардлаас зайлсхийх гэж олон хүн дараагийн "шийдэл"-ийг сонгодог: дугааруудыг таслалаар тусгаарлан нэг текстэн нүдэнд бичих.

authors (буруу хувилбар)
id | name             | book_ids
---+------------------+---------
1  | Д.Нацагдорж      | 1,2
2  | Ч.Лодойдамба     | 3
3  | Robert C. Martin | 4,5,6
4  | Eric Matthes     | 7
5  | Al Sweigart      | NULL

Одоо зохиогч бүр нэг мөртэй болж, гаднаасаа цэгцтэй харагдаж байна. Гэвч энэ хувилбар бүр ч гүн эвдэрсэн. Нэгдүгээрт, '4,5,6' бол текст бөгөөд FOREIGN KEY түүнийг шалгах боломжгүй — '4,5,99' гэж бичсэн ч өгөгдлийн сан саатуулахгүй. Хоёрдугаарт, query бичихэд маш хэцүү: “5-р номыг хэн бичсэн бэ” гэдгийг олохын тулд WHERE book_ids LIKE '%5%' гэж бичихэд л хангалттай мэт — гэвч номын сан 15, 25, 50-р номтой болсон өдөр тэдгээр бүгд ч таарна. Гуравдугаарт, Martin-д дөрөв дэх ном нэмэх нь UPDATE-ээр текстийг задалж, шинэ дугаар залгаж, буцааж бичих болно — нэг жижиг алдаа бүхэл жагсаалтыг эвдэнэ. Өгөгдлийн сангийн хүснэгтийн нэг нүдэнд нэг л утга байх ёстой гэдэг нь харилцан холбоот өгөгдлийн сангийн үндсэн дүрмүүдийн нэг юм.

Зөв хувилбар: заалтыг "олон" талд тавих

Одоо ангийн зүйрлэлийг дагая: заалтыг сурагчийн хуудсанд — өөрөөр хэлбэл номын хүснэгтэд тавина. Ном бүр яг нэг зохиогчтой гэж үзэж байгаа тул ном бүрт ганц нүд хангалттай. Query:

sql

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

INSERT INTO works (title, year, author_id)
VALUES
    ('Учиртай гурван толгой', 1934, 1),
    ('Миний нутаг', 1933, 1),
    ('Тунгалаг Тамир', 1962, 2),
    ('Clean Code', 2008, 3),
    ('The Clean Coder', 2011, 3),
    ('Clean Architecture', 2017, 3),
    ('Python Crash Course', 2015, 4),
    ('Монголын нууц товчоо', 1240, NULL);

works хүснэгт:

id | title                 | year | author_id
---+-----------------------+------+----------
1  | Учиртай гурван толгой | 1934 | 1
2  | Миний нутаг           | 1933 | 1
3  | Тунгалаг Тамир        | 1962 | 2
4  | Clean Code            | 2008 | 3
5  | The Clean Coder       | 2011 | 3
6  | Clean Architecture    | 2017 | 3
7  | Python Crash Course   | 2015 | 4
8  | Монголын нууц товчоо  | 1240 | NULL

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

Мөн хоёр тусгай тохиолдол байгалиараа шийдэгдсэн. Al Sweigart ямар ч номгүй боловч authors хүснэгтэд асуудалгүй оршиж байна — түүн рүү заасан мөр зүгээр л байхгүй. "Монголын нууц товчоо"-ны author_id нь NULL бөгөөд энэ нь "зохиогч нь мэдэгдэхгүй" гэсэн үнэн. Өмнөх хичээлд сурсанчлан FOREIGN KEY нь NULL-ийг шалгадаггүй, тиймээс энэ мөр зөвшөөрөгдсөн. Зохиогчгүй ном байх нь бодит тохиолдол тул бид author_id-д NOT NULL тавиагүй — Бүлэг 4-ийн "энэ утгагүйгээр мөр утга учиргүй болох уу" гэсэн асуултын хариулт энд "үгүй."

Өгөгдлөөр мөшгих: хоёр чиглэлд асуух

Зөв хувилбар хоёр чиглэлийн асуултад хоёуланд нь энгийн хариулт өгдгийг харъя.

Чиглэл 1: нэг зохиогчийн бүх ном. Robert C. Martin ямар ном бичсэн бэ? Query:

sql

SELECT title, year
FROM works
WHERE author_id = 3;

Үр дүн:

title              | year
-------------------+-----
Clean Code         | 2008
The Clean Coder    | 2011
Clean Architecture | 2017

Энэ query юу гэж хэлж байна вэ гэвэл: “works хүснэгтээс author_id нь 3 байгаа мөрүүдийн title болон year-ийг харуул.” Энэ нь ангийн зүйрлэлийн “5А-д хэн хэн суралцдаг вэ” гэсэн асуулт — "олон" талын хүснэгтийг заалтаар нь шүүсэн. Буруу хувилбар 2-ын LIKE-ийн аюул энд огт байхгүй: = 3 нь яг 3-тай л таарна, 13, 30-тай биш.

Чиглэл 2: нэг номын зохиогч. “Python Crash Course”-ийг хэн бичсэн бэ? Одоогоор JOIN мэдэхгүй тул хоёр алхмаар:

sql

SELECT author_id FROM works WHERE title = 'Python Crash Course';

Үр дүн: 4. Дараа нь:

sql

SELECT name FROM authors WHERE id = 4;

Үр дүн:

name
------------
Eric Matthes

Эдгээр нь юу гэж хэлж байна вэ гэвэл: “Python Crash Course-ийн зохиогчийн дугаарыг ол; дараа нь тэр дугаартай зохиогчийн нэрийг харуул.” Ном бүр яг нэг зохиогч руу заадаг тул эхний query яг нэг дугаар буцаасан — ямар ч задлах, давхардал шүүх шаардлагагүй. Энэ хоёр алхмыг нэг query болгон нэгтгэх нь Бүлэг 7-ын JOIN-ий ажил.

Нэмэлт асуулт: номгүй зохиогч. Ямар зохиогчийн ном номын санд байхгүй вэ? Энэ асуулт сонирхолтой, учир нь хариулт нь works хүснэгтэд "байхгүй" зүйлийг хайх юм. Одоогоор бид үүнийг гараар л хийж чадна:

sql

SELECT DISTINCT author_id FROM works ORDER BY author_id;

Үр дүн:

author_id
---------
NULL
1
2
3
4

Энэ query юу гэж хэлж байна вэ гэвэл: “works хүснэгтэд гарч ирсэн бүх давхардалгүй author_id-г эрэмбэлж харуул.” Зохиогчдын жагсаалттай нүдээр харьцуулбал 5 (Al Sweigart) алга. Энэ асуултыг нэг query-ээр хариулах арга бол Бүлэг 7-ын LEFT JOIN-ий "сонгодог" жишээ юм. Мөн Бүлэг 2-ын NOT IN хичээлийг санаж байгаа бол энэ жагсаалтад NULL байгаа нь анхааралтай байх шалтгаан гэдгийг таних болно.

Дүрмийг нэг өгүүлбэрээр

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

Гэхдээ энэ хичээлийн эхэнд хийсэн таамаглалыг шударгаар эргэн харъя: "ном бүр нэг зохиогчтой." Энэ үнэн үү? Бүлэг 3-ын UPDATE хичээлд бид “The Pragmatic Programmer”-ийн зохиогчийг “Andrew Hunt, David Thomas” гэж засаж байсан — нэг нүдэнд хоёр нэр, Буруу хувилбар 2-ын асуудал. Олон ном хамтран зохиогчтой байдаг. Хэрэв "нэг ном олон зохиогчтой байж болно" гэж хүлээн зөвшөөрвөл харилцаа нэг-олон биш, олон-олон болно — тэгвэл FOREIGN KEY аль ч хүснэгтэд багтахгүй. Энэ нь дараагийн хичээлийн сэдэв юм.

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

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

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

Хэрэв сонирхож байвал энэ хичээлийн хүснэгтүүдийг дахин үүсгээд Al Sweigart-ийн “Automate the Boring Stuff with Python” (2015) номыг нэмж, Чиглэл 1-ийн query-г түүний дугаараар ажиллуулж үзээрэй. Мөн өөрийн амьдралаас нэг-олон харилцааны гурван жишээ олоод (жишээ нь хот ба дүүрэг, баг ба тамирчин, гэр бүл ба гишүүн), тус бүрд "нэг ... нэг" өгүүлбэрийг хэлж, FOREIGN KEY аль хүснэгтэд байх ёстойг шийдэж үзээрэй. Дуусаад Revert Changes дарна. Эдгээр нь зөвхөн санал бөгөөд дараагийн хичээлүүд тэдгээрийг хийсэн гэж тооцохгүй.

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

Заалтыг "нэг" талд тавих. Буруу хувилбар 1-д харсанчлан энэ нь ямар ч алдааны мессеж өгөхгүй — хүснэгт амжилттай үүсч, эхний хэдэн мөр асуудалгүй нэмэгдэнэ. Асуудал анхны "олон" тохиолдол ирэхэд л илэрнэ: давхардсан мөрүүд, худал тоолол. Хүснэгт зохиохдоо "нэг ... нэг" өгүүлбэрийн дүрмийг шалгаарай.

Нэг нүдэнд жагсаалт хадгалах. Буруу хувилбар 2 бол хамгийн түгээмэл бөгөөд хамгийн удаан илэрдэг алдаа юм. '4,5,6' гэх мэт таслалтай текст, эсвэл “Andrew Hunt, David Thomas” гэх мэт нэг нүдэнд олон нэр харагдвал энэ нь хүснэгтийг хуваах хэрэгтэйн дохио юм.

Эцэг эхээс өмнө хүүхдийг нэмэх. Хэрэв authors хүснэгтийг дүүргэхээс өмнө works-ийн INSERT-ийг ажиллуулбал FOREIGN KEY constraint failed гарна. Энэ мессеж юу гэж хэлж байна вэ гэвэл: "гадаад түлхүүрийн дүрэм зөрчигдлөө" — заагдах зохиогч хараахан байхгүй. Өгөгдөл оруулахдаа "нэг" талыг (зохиогч) эхлээд, "олон" талыг (ном) дараа нь оруулна. Устгахдаа эсрэгээр: эхлээд номнуудыг, дараа нь зохиогчийг — өмнөх хичээлийн "хүүхдийг эхэлж, эцэг эхийг дараа нь" дүрэм.

Буруу боловч байгаа мөр рүү заах. “Python Crash Course”-ийг нэмэхдээ author_id-д 4-ийн оронд санамсаргүй 3 гэж бичвэл FOREIGN KEY юу ч хэлэхгүй — 3-р зохиогч байгаа тул заалт хүчинтэй. Үр дүнд нь Robert C. Martin дөрөв дэх номтой болно. FOREIGN KEY нь заалт байгаа мөр рүү чиглэж байгааг баталдаг, харин зөв мөр рүү чиглэж байгааг баталж чадахгүй. Энэ төрлийн алдаанаас зөвхөн өгөгдөл оруулах үеийн анхаарал, эсвэл INSERT-ийн дараа SELECT-ээр шалгах дадал хамгаална.

Зохиогчийн нэрийг текстээр давхар хадгалах. Бидний эхлэл books хүснэгт author TEXT гэсэн баганатай — өөрөөр хэлбэл зохиогчийн нэрийг ном бүрт текстээр бичдэг. Энэ нь жижиг хүснэгтэд ажилладаг ч хэн нэгэн "Д.Нацагдорж"-ийг нэг мөрөнд "Нацагдорж Д." гэж бичвэл өгөгдлийн сан тэднийг хоёр өөр хүн гэж үзнэ. Тусдаа authors хүснэгт ба author_id заалт нь зохиогчийн нэрийг ганцхан газар хадгалж энэ асуудлыг бүрмөсөн шийддэг. Энэ шилжилтийг Түвшин 2-ын удирдамжтай дасгалд хийнэ.

Товч дүгнэлт

Энэ хичээлд та нэг-олон харилцааг хэрхэн зохиохыг өгөгдлөөр мөшгиж сурлаа. Заалтыг "нэг" талд тавих нь өгөгдлийг давхардуулж, худал тоолол үүсгэдэг; жагсаалтыг нэг нүдэнд хадгалах нь FOREIGN KEY-ийн хамгаалалтыг алдагдуулж, query-г эмзэг болгодог. Зөв хувилбар нь FOREIGN KEY-г "олон" талд — мөр бүр яг нэгийг заадаг хүснэгтэд — тавих бөгөөд "Нэг ном нэг зохиогчтой" гэх мэт "нэг ... нэг" өгүүлбэр түүний гэрийг заана. Энэ хувилбар хоёр чиглэлийн асуултад хоёуланд нь энгийн хариулт өгч, номгүй зохиогч болон зохиогчгүй номыг байгалиараа хүлээн авдаг. Өгөгдөл оруулахдаа "нэг" талыг эхэлж, устгахдаа "олон" талыг эхэлнэ.

Дараагийн хичээлд бид энэ хичээлийн таамаглалыг эвдэх харилцааг судална: нэг ном олон зохиогчтой, нэг зохиогч олон номтой байж болох үед. Үүнтэй адил олон-олон харилцаа номын сангаас гадна хаа сайгүй бий — нэг сурагч олон сургалтад, нэг сургалт олон сурагчтай. Ийм харилцааг FOREIGN KEY-ийг аль ч хүснэгтэд шууд тавьж шийдэх боломжгүй бөгөөд тусгай холбогч хүснэгт хэрэгтэй болно.