SQL: екі кесте, JOIN және сыртқы кілттер

Не үшін керек

Өткен сабақта бір кесте болды, әрі бәрі қарапайым еді: жол — бұл мақала.

Енді мақалаға пікірлер келеді. Бір мақалада олар нөл де, елу де болуы мүмкін, ал сіз мұны алдын ала білмейсіз. Оларды сол жолға салатын жер жоқ: comment1, comment2, comment3 бағандары — схема емес, тығырық.

Дұрыс жауап — біріншіге сілтейтін екінші кесте. Бүгін оны қалай байланыстыруды, екеуін бір жауапқа қалай жинауды және мақала өшірілгенде пікірлерге не болатынын талдаймыз.

Бірден тұтас

Өткен сабақтағы сол blog.db-ны ашыңыз — сол үш баптау жолымен:

.mode box
.headers on
pragma foreign_keys = on;

Екінші кесте:

create table comments (
    id         integer primary key,
    article_id integer not null references articles(id) on delete cascade,
    author     text    not null,
    body       text    not null
) strict;

Мұндағы кілт жол біреу, әрі оны бірінші болып талдаймыз.

Талдау

Екінші кесте біріншіге сілтейді

Пікірлер мақаланың ішінде жатпайды. Олар бөлек кестеде жатады да, мақалаға санмен сілтейді:

article_id integer not null references articles(id) on delete cascade

Бұл — сыртқы кілт: comments.article_id ішінде articles.id жатады. references дерекқорға онда бөтен нөмір болмауы керегін айтады, ал on delete cascade — мақаламен бірге оның пікірлері де кететінін.

Пікірлер әзірге жоқ — үшеуін қосайық, біріншісі мен екінші мақалаға:

insert into comments (article_id, author, body) values
  (1, 'Әсел', 'Жақсы мақала'),
  (1, 'Дана', 'Рақмет'),
  (2, 'Әсел', 'Шаңырақ туралы болады ма?');

join: жұбы барлар ғана

Пікірлерді мақалалармен бірге жинау — join жұмысы:

select a.title, c.author, c.body
from articles a
join comments c on c.article_id = a.id
order by a.id, c.id;

┌──────────────────┬────────┬───────────────────────────┐
│      title       │ author │           body            │
├──────────────────┼────────┼───────────────────────────┤
│ Дала туралы      │ Әсел   │ Жақсы мақала              │
│ Дала туралы      │ Дана   │ Рақмет                    │
│ Шаңырақ деген не │ Әсел   │ Шаңырақ туралы болады ма? │
└──────────────────┴────────┴───────────────────────────┘

join тек сәйкестік табылған жұптарды қалдырады. Пікірсіз мақалалар мұндай жауапқа түспейді — ал басты бетте олар нөлмен болса да керек.

left join: жұбы жоқтар да

Ол сол жақ кестенің барлық жолын алады, ал жұп болмаса, оң жағына бос қояды.

select a.title, count(c.id) as пікірлер
from articles a
left join comments c on c.article_id = a.id
group by a.id, a.title
order by пікірлер desc, a.id;

┌──────────────────┬──────────┐
│      title       │ пікірлер │
├──────────────────┼──────────┤
│ Дала туралы      │ 2        │
│ Шаңырақ деген не │ 1        │
│ Сәлем, әлем      │ 0        │
│ Жоба             │ 0        │
└──────────────────┴──────────┘

count(c.id) пікірлерді санайды, group by жолдарды мақала бойынша топқа жинайды. Соңғы екеуіндегі нөлдерге назар аударыңыз: кәдімгі join оларды көрсетпес те еді.

SQLite тұзағы: сыртқы кілттер өшірулі

Мақаланы өшіріп, онымен бірге пікірлер кеткенін тексерейік:

delete from articles where slug = 'dala';
select count(*) as қалған_пікірлер from comments;

┌─────────────────┐
│ қалған_пікірлер │
├─────────────────┤
│ 1               │
└─────────────────┘

Пікір үшеу еді, екеуі өшірілген мақалаға қатысты — біреуі қалды. on delete cascade істеді.

Ол істеді, өйткені ең басында біз былай жаздық:

pragma foreign_keys = on;

Бұл жолсыз кілттер туралы жоғарыда жазылғанның ешқайсысы істемейді. Жаңа қосылымда тексеріңіз:

pragma foreign_keys;

┌──────────────┐
│ foreign_keys │
├──────────────┤
│ 0            │
└──────────────┘

Нөл. SQLite тарихи себептермен сыртқы кілттерді тексеруді әдепкіде өшірулі ұстайды, әрі оны әр қосылымда қайта қосу керек. Үнсіз, қатесіз: references схемаға жазылады да, тексерілмейді, ал on delete cascade пікірлерді жетім қалдырады.

Келесі сабақта дерекқорға Go қосылғанда, сіз ең бірінші осыны істейсіз.

Сабақ картасы

Өз сөзіңізбен айтыңыз

Қарамай, дауыстап немесе қағазға жауап беріңіз. Жауаптар — сабақтың соңында.

1. Пікірлер неге articles ішіндегі бағандар емес, бөлек кесте болып жатыр?
2. join left join-нан несімен өзгеше және айырма қашан көрінеді?
3. pragma foreign_keys = on ұмытылса, on delete cascade немен бітеді?

Тапсырма

Міндетті. Үшінші tags кестесін жасап, оны мақалалармен байланыстырыңыз. Бір мақалада бірнеше тег болуы мүмкін, ал бір тег бірнеше мақалада тұруы мүмкін, сондықтан байланысқа екі бағаннан тұратын бөлек кесте керек. Мақалаларды тегтерімен шығаратын сұраныс жазыңыз, әрі екіншісін — әр тегте қанша мақала бар екенін санайтын.

Қалауыңызша.

• Жоқ нөмірлі мақалаға пікір салыңыз да, дерекқор не айтатынын оқыңыз. Содан кейін pragma foreign_keys дегенді өшіріп, қайталаңыз.
• Санайтын сұраныстағы left join-ды join-ға ауыстырып, екі жол қайда кеткенін түсіндіріңіз.
• Пікірі жоқ мақаланы өшіріп, пікірлер саны өзгермегеніне көз жеткізіңіз.

Блогта бұл қайда тұрады

Екі сабақтағы схема — блогтың схемасы. Келесі сабақта оған Go қосылады: database/sql, драйвер және тілімнен емес, файлдан оқылған алғашқы жол.

Қарыздар. Сұраныстарды консольде қолмен жаздық — Go-дан оларды бөтен мәтін пәрменнің бөлігіне айнала алмайтындай жіберу керек; бұл — инъекциялар сабағы. Әрі pragma foreign_keys дегенді әр қосылымда қосуға тура келеді, ал бағдарламада қосылым бірнешеу болады — келесі сабақ бұл туралы бөлек айтады.

Жауаптар

Дереккөздер

• SQLite: сыртқы кілттер
• SQLite: сұраныс тілі
• SQLite: қатаң кестелер
• PostgreSQL оқулығы: нөлден бастап SQL