Ключі й звʼязки: бази даних для PHP-розробника

01 · КЛЮЧ І ПОСИЛАННЯ

Рядок, на який можна послатися

однозначно називає рядок: він не NULL, не повторюється і в таблиці лише один. Найчастіше це id, який нічого не означає поза базою. Буває й , узятий з самих даних: email, код валюти, назва пакета на Packagist.

СЛОВНИКПервинний ключ. PRIMARY KEY: NOT NULL плюс унікальність, один на таблицю. База одразу створює під нього унікальний індекс. В InnoDB це ще й кластерний індекс: рядки таблиці лежать у його листках.

СЛОВНИКСурогатний ключ. Ключ, який база або застосунок генерує сам: лічильник, ULID, UUID. Він ніколи не змінюється, бо нічого не означає. Його протилежність це природний ключ.

СЛОВНИКПриродний ключ. Унікальна властивість самих даних: email, ISO-код валюти, vendor/package. Зручний, поки не змінюється. Коли змінюється, разом з ним треба переписати всі посилання.

у vacancies.company_id каже базі: тут може стояти лише id, який є в companies. Вакансію неіснуючої компанії база не вставить, а при видаленні компанії зробить з її вакансіями те, що сказано в ON DELETE. Без обмеження ця перевірка живе тільки в коді, і перший скрипт імпорту чи ручний DELETE у консолі її обійде.

СЛОВНИКЗовнішній ключ. FOREIGN KEY (company_id) REFERENCES companies (id): значення має існувати в батьківській таблиці або бути NULL. ON DELETE і ON UPDATE кажуть, що робити з дочірніми рядками, коли батьківський зникає чи змінює ключ.

Один до багатьох виражається колонкою на боці «багатьох»: у компанії багато вакансій, тому company_id лежить у vacancies. Багато до багатьох так не записати: вакансія вимагає кількох навичок, а навичка трапляється в сотнях вакансій. Для цього потрібна третя таблиця, , де кожен рядок це одна пара. Один до одного це звичайний зовнішній ключ з унікальним індексом.

СЛОВНИКPivot. Проміжна таблиця звʼязку багато до багатьох, junction table. Два зовнішні ключі, а пара з них зазвичай і є первинним ключем. Laravel називає її з імен двох моделей в однині за абеткою: skill_vacancy.

02 · ЯКИЙ ПЕРВИННИЙ КЛЮЧ

Куди лягає новий рядок

В InnoDB рядки фізично впорядковані за первинним ключем. Ключ, що зростає, дописує новий рядок у праву сторінку дерева, яка й так у памʼяті. Випадковий ключ кидає кожну вставку в іншу сторінку: її треба прочитати з диска, вона ділиться навпіл і лишається напівпорожньою. У PostgreSQL таблиця не впорядкована за ключем, але індекс первинного ключа страждає так само.

Замір на 2 000 000 рядків з буфером 128 MB: в InnoDB вставка з BIGINT AUTO_INCREMENT зайняла 19 секунд і 264 MB, з випадковими 16 байтами 330 секунд і 452 MB, з 16 байтами, що зростають, як у UUIDv7, знову 19 секунд і 280 MB. У PostgreSQL 16 вставка з gen_random_uuid() тривала 18,7 секунди проти 7,5 з IDENTITY, а індекс ключа вийшов 75 MB проти 43 MB.

PRIMARY KEY і вставка нових рядків

BIGINT AUTO_INCREMENT · GENERATED AS IDENTITY послідовний Кожен новий id більший за попередній, рядок дописується в кінець дерева. 8 байтів у кожному індексі. Мінус: id видно в URL, і за ним легко перебрати сусідні записи або порахувати, скільки їх.

ULID · UUIDv7 зростає з часом Перші 48 біт це час у мілісекундах, тож нові ключі лягають поруч. Ключ можна згенерувати в застосунку ще до INSERT, і він нічого не каже про кількість записів. Текстом це char(26) чи char(36), у 3-4 рази довше за bigint, і так у кожному індексі.

UUIDv4 випадковий Кожна вставка в іншу сторінку. У замірі InnoDB вставка в 17 разів повільніша, таблиця на 70% більша. Вторинні індекси InnoDB зберігають первинний ключ, тож вони теж ростуть.

email як PRIMARY KEY змінюється Унікальний, але не вічний: людина змінює пошту, і разом з нею треба оновити всі таблиці, що на неї посилаються. Надійніше сурогатний id і UNIQUE на email.

PRIMARY KEY (vacancy_id, skill_id) для pivot Пара сама унікальна, окремий id не потрібен. Порядок колонок важить так само, як у складеному індексі: цей ключ знаходить навички вакансії, а для вакансій за навичкою потрібен ще індекс на skill_id.

Заміри на MySQL 8.0.46 і PostgreSQL 16 в одному контейнері, 2 000 000 рядків, по 100 байтів даних у рядку. Числа це приклад: на іншому залізі вони будуть інші, співвідношення схожі.

03 · ЗОВНІШНІЙ КЛЮЧ

База не дасть послатися в нікуди

ON DELETE каже, що робити з вакансіями, коли видаляють компанію. RESTRICT і NO ACTION (за замовчуванням) відмовляють, поки на компанію хтось посилається. CASCADE видаляє вакансії разом з нею. SET NULL лишає вакансії, але обнуляє company_id, тож колонка має бути nullable.

У MySQL RESTRICT і NO ACTION однакові. У PostgreSQL NO ACTION можна відкласти до кінця транзакції (DEFERRABLE), а RESTRICT перевіряється одразу. SQLite має пастку: зовнішні ключі вимкнені, поки зʼєднання не виконає PRAGMA foreign_keys = ON. Laravel вмикає їх сам: у config/database.php для sqlite стоїть foreign_key_constraints.

MySQL 8.0 PostgreSQL 16 SQLite 3.45

1CREATE TABLE vacancies (

2 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

3 company_id BIGINT UNSIGNED NOT NULL,

4 title VARCHAR(300) NOT NULL,

5 CONSTRAINT vacancies_company_id_foreign FOREIGN KEY (company_id)

6 REFERENCES companies (id) ON DELETE RESTRICT

7);

9INSERT INTO vacancies (company_id, title) VALUES (99, 'Ghost');

10DELETE FROM companies WHERE id = 1;

ERROR 1452 (23000): Cannot add or update a child row:
  a foreign key constraint fails (`lab`.`vacancies`, CONSTRAINT …)
ERROR 1451 (23000): Cannot delete or update a parent row:
  a foreign key constraint fails (`lab`.`vacancies`, CONSTRAINT …)

Ключ оголошено окремим рядком CONSTRAINT … FOREIGN KEY. Скорочений запис company_id … REFERENCES companies (id) прямо в описі колонки MySQL 8.0 мовчки ігнорує: таблиця створиться без ключа.

1CREATE TABLE vacancies (

2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

3 company_id BIGINT NOT NULL,

4 title VARCHAR(300) NOT NULL,

5 CONSTRAINT vacancies_company_id_foreign FOREIGN KEY (company_id)

6 REFERENCES companies (id) ON DELETE RESTRICT

7);

9INSERT INTO vacancies (company_id, title) VALUES (99, 'Ghost');

10DELETE FROM companies WHERE id = 1;

ERROR:  insert or update on table "vacancies" violates foreign key constraint "vacancies_company_id_foreign"
DETAIL:  Key (company_id)=(99) is not present in table "companies".
ERROR:  update or delete on table "companies" violates foreign key constraint "vacancies_company_id_foreign" on table "vacancies"
DETAIL:  Key (id)=(1) is still referenced from table "vacancies".

DETAIL одразу називає ключ, що завадив. IDENTITY це стандартний SQL на заміну serial; Laravel для id() досі пише bigserial, і працюють вони однаково.

1CREATE TABLE vacancies (

2 id INTEGER PRIMARY KEY AUTOINCREMENT,

3 company_id INTEGER NOT NULL

4 REFERENCES companies (id) ON DELETE RESTRICT,

5 title VARCHAR NOT NULL

6);

8PRAGMA foreign_keys; -- 0

9INSERT INTO vacancies (company_id, title) VALUES (99, 'Ghost');

11PRAGMA foreign_keys = ON;

12INSERT INTO vacancies (company_id, title) VALUES (98, 'Ghost 2');

0
-- перша вставка пройшла: ключ є, але не перевіряється
Runtime error: FOREIGN KEY constraint failed (19)

PRAGMA діє на одне зʼєднання і не зберігається у файлі бази. Звідси клас багів «у тестах падає, у консолі sqlite3 ні» і навпаки.

04 · ІНДЕКС ПІД ЗОВНІШНІЙ КЛЮЧ

InnoDB створює його сам, PostgreSQL ні

Щоб видалити компанію, база мусить знайти її вакансії: WHERE company_id = ?. Без індексу на company_id це перебір усієї таблиці вакансій на кожну видалену компанію, і так само для UPDATE ключа батьківського рядка.

InnoDB вимагає індекс під зовнішнім ключем. Якщо підходящого немає (колонка ключа першою в якомусь індексі), він створює його сам з назвою обмеження і не дасть його видалити. PostgreSQL і SQLite індекс на колонці, яка посилається, не створюють: про нього треба подбати в міграції.

MySQL 8.0 PostgreSQL 16 SQLite 3.45

1SHOW INDEX FROM vacancies;

3ALTER TABLE vacancies

4 DROP INDEX vacancies_company_id_foreign;

Key_name                      Column_name
PRIMARY                       id
vacancies_company_id_foreign  company_id

ERROR 1553 (HY000): Cannot drop index 'vacancies_company_id_foreign':
  needed in a foreign key constraint

Ми не просили індекс, InnoDB додав його разом з ключем. Якщо потім додати інший індекс з company_id першою колонкою, InnoDB мовчки прибере свій, і ключ перейде на новий.

1\d vacancies

Indexes:
    "vacancies_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "vacancies_company_id_foreign" FOREIGN KEY (company_id)
      REFERENCES companies(id) ON DELETE RESTRICT

Обмеження є, індексу на company_id немає. Індекс створюється лише під PRIMARY KEY, UNIQUE і EXCLUDE.

1EXPLAIN QUERY PLAN

2SELECT * FROM vacancies WHERE company_id = 7;

`--SCAN vacancies

SCAN це перебір таблиці. Саме такий пошук SQLite робить при кожному видаленні компанії, поки немає CREATE INDEX на company_id.

05 · БАГАТО ДО БАГАТЬОХ

Таблиця, де рядок це пара

Вакансія вимагає Laravel і Docker, Laravel потрібен у багатьох вакансіях. Таблиця skill_vacancy тримає по рядку на кожну пару. Первинний ключ (vacancy_id, skill_id) не пустить ту саму пару двічі, і окремий id не потрібен.

Індекс первинного ключа впорядкований спершу за vacancy_id. Він швидко відповідає «які навички в цієї вакансії», а для «які вакансії вимагають Laravel» потрібен другий індекс, на skill_id. В InnoDB він ще й стане індексом під зовнішній ключ skill_id.

MySQL 8.0 PostgreSQL 16 SQLite 3.45

1CREATE TABLE skill_vacancy (

2 vacancy_id INT NOT NULL,

3 skill_id INT NOT NULL,

4 PRIMARY KEY (vacancy_id, skill_id),

5 INDEX skill_vacancy_skill_id_index (skill_id),

6 FOREIGN KEY (vacancy_id) REFERENCES vacancies (id) ON DELETE CASCADE,

7 FOREIGN KEY (skill_id) REFERENCES skills (id) ON DELETE CASCADE

8);

10SELECT v.title, s.name

11FROM vacancies v

12JOIN skill_vacancy sv ON sv.vacancy_id = v.id

13JOIN skills s ON s.id = sv.skill_id

14WHERE s.name = 'Laravel';

16INSERT INTO skill_vacancy VALUES (10, 1);

+-------------+---------+
| title       | name    |
+-------------+---------+
| Senior PHP  | Laravel |
| Laravel Dev | Laravel |
+-------------+---------+
ERROR 1062 (23000): Duplicate entry '10-1' for key 'skill_vacancy.PRIMARY'

Звʼязок багато до багатьох у запиті це два JOIN через pivot. Повторна пара впирається в первинний ключ.

1CREATE TABLE skill_vacancy (

2 vacancy_id INT NOT NULL REFERENCES vacancies (id) ON DELETE CASCADE,

3 skill_id INT NOT NULL REFERENCES skills (id) ON DELETE CASCADE,

4 PRIMARY KEY (vacancy_id, skill_id)

5);

6CREATE INDEX skill_vacancy_skill_id_index ON skill_vacancy (skill_id);

8INSERT INTO skill_vacancy VALUES (10, 1);

ERROR:  duplicate key value violates unique constraint "skill_vacancy_pkey"
DETAIL:  Key (vacancy_id, skill_id)=(10, 1) already exists.

У PostgreSQL REFERENCES прямо в описі колонки працює. Той самий SELECT з двома JOIN повертає ті самі два рядки.

1CREATE TABLE skill_vacancy (

2 vacancy_id INT NOT NULL REFERENCES vacancies (id) ON DELETE CASCADE,

3 skill_id INT NOT NULL REFERENCES skills (id) ON DELETE CASCADE,

4 PRIMARY KEY (vacancy_id, skill_id)

5);

6CREATE INDEX skill_vacancy_skill_id_index ON skill_vacancy (skill_id);

8INSERT INTO skill_vacancy VALUES (10, 1);

Runtime error: UNIQUE constraint failed: skill_vacancy.vacancy_id, skill_vacancy.skill_id (19)

Таблиця з складеним первинним ключем у SQLite має прихований rowid, а пара (vacancy_id, skill_id) лежить в окремому унікальному індексі.

06 · ВИДАЛЕННЯ БЕЗ ІНДЕКСУ

Скільки коштує видалити одну компанію

У PostgreSQL перевірка зовнішнього ключа працює як системний тригер, і EXPLAIN ANALYZE показує його окремим рядком. Таблиця vacancies на мільйон рядків, видаляємо компанію без жодної вакансії: сам DELETE займає соті мілісекунди, а перевірка, що на компанію ніхто не посилається, без індексу читає всю таблицю. EXPLAIN ANALYZE видаляє насправді, тому він загорнутий у BEGIN і ROLLBACK.

без індексуDELETE FROM companies WHERE id = 10000;

Delete on companies (actual time=0.042..0.043 rows=0 loops=1)
  ->  Index Scan using companies_pkey on companies (actual time=0.022..0.023 rows=1 loops=1)
        Index Cond: (id = 10000)
Trigger for constraint vacancies_company_id_foreign: time=63.512 calls=1
Execution Time: 63.621 ms

з індексомDELETE FROM companies WHERE id = 10000;

Delete on companies (actual time=0.035..0.035 rows=0 loops=1)
  ->  Index Scan using companies_pkey on companies (actual time=0.019..0.020 rows=1 loops=1)
        Index Cond: (id = 10000)
Trigger for constraint vacancies_company_id_foreign: time=0.270 calls=1
Execution Time: 0.323 ms

PostgreSQL 16, 10 000 компаній і 1 000 000 вакансій, ON DELETE SET NULL. EXPLAIN (ANALYZE, COSTS OFF), рядки Planning Time прибрано.

індекс від InnoDBSELECT * FROM vacancies WHERE company_id = 7;

type	key	rows	Extra
ref	vacancies_company_id_foreign	101	NULL

MySQL 8.0.46, InnoDB, та сама таблиця на 1 000 000 рядків. Вивід знято зі справжньої бази, частину колонок прибрано.

Trigger for constraint: Час, витрачений на перевірку зовнішнього ключа: пошук у vacancies рядків з цим company_id. Видно лише в EXPLAIN ANALYZE, бо план самого DELETE його не містить.
calls: Скільки разів тригер спрацював, по одному на видалений рядок. Видалення тисячі компаній без індексу означає тисячу переборів таблиці.
Execution Time: Повний час разом з тригерами: 63 мс без індексу проти 0,3 мс з індексом vacancies_company_id_index.

key: Індекс, який InnoDB створив разом із зовнішнім ключем. Ним же користується перевірка при видаленні компанії, тож тут вона завжди швидка.
rows: Оцінка рядків, які доведеться прочитати: вакансії однієї компанії, а не мільйон.

07 · КЛЮЧІ В МІГРАЦІЯХ LARAVEL

Як оголосити звʼязок у міграції

foreignId() створює колонку unsigned bigint під id(), constrained() додає обмеження. Назву обмеження Laravel складає сам: таблиця, колонка і foreign. Саме її ви побачите в повідомленні про помилку.

php create_skill_vacancy_table.php Один до багатьох

1Schema::create('vacancies', function (Blueprint $table) {

2 $table->id();

3 // company_id → companies.id, обмеження vacancies_company_id_foreign

4 $table->foreignId('company_id')->constrained()->cascadeOnDelete();

5 $table->index('company_id'); // для PostgreSQL і SQLite

6});

ЩО ПЕРЕВІРЯЄ: constrained() без аргументів бере таблицю з назви колонки: відкидає _id і ставить множину, company_id → companies. Інша таблиця передається першим аргументом: constrained('users'). На MySQL зайвого індексу не буде: свій автоматичний InnoDB прибирає, щойно зʼявляється придатний.
КОЛИ БРАТИ: Для кожного поля, яке посилається на інший рядок. Індекс окремо, якщо продакшн на PostgreSQL і за колонкою шукають або видаляють батьківські рядки.

php create_skill_vacancy_table.php ON DELETE

1$table->foreignId('company_id')->constrained()->cascadeOnDelete(); // CASCADE

2$table->foreignId('company_id')->nullable()->constrained()->nullOnDelete(); // SET NULL

3$table->foreignId('company_id')->constrained()->restrictOnDelete(); // RESTRICT

4$table->foreignId('company_id')->constrained()->noActionOnDelete(); // NO ACTION

ЩО ПЕРЕВІРЯЄ: Без жодного з методів у SQL не буде ON DELETE, і база застосує NO ACTION. nullOnDelete() без nullable() MySQL не створить зовсім (ERROR 1830), а PostgreSQL і SQLite створять і впадуть на першому видаленні компанії з вакансіями: NULL не записати в NOT NULL колонку.
КОЛИ БРАТИ: CASCADE для даних, що без батька не мають сенсу: відгуки вакансії. SET NULL коли дочірній рядок живе й сам: вакансія без компанії. RESTRICT коли видалення батька має бути свідомим кроком.

php create_skill_vacancy_table.php Pivot

1// Модель Vacancy: belongsToMany(Skill::class) шукає таблицю skill_vacancy.

2Schema::create('skill_vacancy', function (Blueprint $table) {

3 $table->foreignId('vacancy_id')->constrained()->cascadeOnDelete();

4 $table->foreignId('skill_id')->constrained()->cascadeOnDelete();

6 $table->primary(['vacancy_id', 'skill_id']);

7 $table->index('skill_id');

8});

ЩО ПЕРЕВІРЯЄ: Назву belongsToMany() складає з імен двох моделей в однині, відсортованих за абеткою. primary() на дві колонки не пустить дубль пари, а attach() тієї самої навички вдруге впаде з помилкою унікальності.
КОЛИ БРАТИ: Коли звʼязок не несе власних даних або несе кілька полів (withPivot, withTimestamps). Якщо в пари є свій життєвий цикл, як у відгуку, це вже окрема сутність з власним id.

php create_skill_vacancy_table.php ULID і UUID

1// Модель: use HasUlids; ключ генерується в PHP до INSERT.

2$table->ulid('id')->primary(); // char(26)

3$table->foreignUlid('vacancy_id')->constrained();

5// use HasUuids; у Laravel 13 це UUIDv7, що зростає з часом.

6$table->uuid('id')->primary(); // char(36) у MySQL, uuid у PostgreSQL

ЩО ПЕРЕВІРЯЄ: HasUlids і HasUuids заповнюють ключ у PHP перед INSERT, тож рядок не чекає, поки база видасть id. Обидва ключі зростають з часом і не розкидають вставки по дереву.
КОЛИ БРАТИ: Коли id потрапляє в публічні URL, коли записи створюють у кількох місцях без спільного лічильника або коли id потрібен до INSERT. В інших випадках id() компактніший.

php create_skill_vacancy_table.php Один до одного

1Schema::create('developer_profiles', function (Blueprint $table) {

2 $table->id();

3 $table->foreignId('user_id')->unique()->constrained('users')->cascadeOnDelete();

4});

ЩО ПЕРЕВІРЯЄ: unique() на зовнішньому ключі не дасть створити другий профіль того самого користувача. Унікальний індекс одночасно служить індексом під ключ.
КОЛИ БРАТИ: Коли в сутності є необовʼязкова частина зі своїм набором полів: профіль розробника в користувача.

08 · ЯК У PHP UKRAINE

Пошук роботи як pivot з даними

Таблиця job_search_items звʼязує користувача з вакансією, яку він зберіг або на яку відгукнувся, і несе власні поля: етап, нотатку, наступний крок. Це звʼязок багато до багатьох з даними, тож у нього свій id, а пару стереже unique(user_id, vacancy_id). Складений первинний ключ тут не підійшов би: vacancy_id буває NULL, коли людина вручну додає вакансію з іншого сайту, а колонки первинного ключа NULL не допускають. Унікальний індекс допускає скільки завгодно NULL, бо NULL не дорівнює NULL, тож зовнішніх вакансій у людини може бути багато. Ліва колонка цього індексу, user_id, служить і індексом під зовнішній ключ. А під vacancy_id і application_id окремого індексу немає: на PostgreSQL видалення вакансії чи відгуку перебиратиме цю таблицю.

php 2026_09_10_000003_create_job_search.php фрагмент, скорочено

1Schema::create('job_search_items', function (Blueprint $table) {

2 $table->id();

3 $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();← індекс через unique нижче

4 $table->foreignId('vacancy_id')->nullable()->constrained('vacancies')->cascadeOnDelete();← NULL для вакансії з іншого сайту

5 $table->string('external_url', 500)->nullable();

6 $table->string('stage', 20)->default('saved');

7 $table->foreignId('application_id')->nullable()->constrained('applications')->nullOnDelete();← без індексу в PostgreSQL

9 $table->unique(['user_id', 'vacancy_id']);← пара, і NULL не заважає

10 $table->index(['user_id', 'stage']);

11});

database/migrations/2026_09_02_000002_create_vacancies_table.phpvacancies.company_id з nullOnDelete: якщо компанію видалити, її вакансії лишаються в каталозі без компанії. Тому колонка nullable.

database/migrations/2026_09_04_000001_add_employer_cabinet.phpapplications посилається і на вакансію, і на компанію, обидва з cascadeOnDelete: відгук без вакансії не має сенсу. users.company_id з nullOnDelete: людина лишається, коли зникає компанія.

database/migrations/2026_09_04_000002_create_developer_profiles.phpОдин до одного: developer_profiles.user_id з unique() і cascadeOnDelete.

database/migrations/2026_09_03_000004_create_technologies_and_packages_tables.phpПриродний ключ: у packages первинний ключ це назва пакета vendor/package, бо під нею його знає Packagist і під нею сайт оновлює його числа.

09 · ТИПОВІ ПОМИЛКИ

Де ключі підводять

✕ 1

Звʼязок без обмеження

company_id є, FOREIGN KEY немає, бо «ORM і так стежить». Потім хтось видаляє компанію в консолі, і в каталозі зʼявляються вакансії з company_id, якого немає. JOIN тихо їх відкидає, а LEFT JOIN показує NULL.

Натомість: constrained() у кожній міграції. Знайти сиріт до додавання ключа: SELECT … FROM vacancies v LEFT JOIN companies c ON c.id = v.company_id WHERE v.company_id IS NOT NULL AND c.id IS NULL.

✕ 2

Немає індексу під ключем у PostgreSQL

Обмеження є, індексу немає. Кожне видалення батьківського рядка перебирає дочірню таблицю, а JOIN за цією колонкою теж іде без індексу.

Натомість: $table->index('company_id') поруч з constrained() або складений індекс, де ця колонка перша.

✕ 3

CASCADE там, де дані треба зберегти

ON DELETE CASCADE на історичних даних: видалили вакансію, разом з нею зникли всі відгуки на неї, а з ними й історія кандидатів. Каскад іде ланцюжком і може зачепити кілька таблиць за один DELETE.

Натомість: Для того, що має пережити батька, беріть SET NULL або RESTRICT і закривайте записи статусом замість видалення.

✕ 4

REFERENCES в описі колонки в MySQL

company_id INT REFERENCES companies (id) у MySQL 8.0 проходить без помилки, але ключа не створює. Вставка неіснуючого id теж пройде.

Натомість: Окремий рядок CONSTRAINT … FOREIGN KEY (…) REFERENCES … або міграція Laravel, яка пише саме так. Перевірка: SHOW CREATE TABLE.

✕ 5

UUIDv4 як первинний ключ InnoDB

Кожна вставка в випадкову сторінку дерева, таблиця розпухає, а всі вторинні індекси тягнуть по 36 байтів ключа замість 8.

Натомість: id() для внутрішніх таблиць, ULID чи UUIDv7 (HasUlids, HasUuids) там, де потрібен непередбачуваний чи заздалегідь відомий id.

10 · СЛОВНИК

Первинний ключ: PRIMARY KEY: NOT NULL плюс унікальність, один на таблицю. База одразу створює під нього унікальний індекс. В InnoDB це ще й кластерний індекс: рядки таблиці лежать у його листках.
Сурогатний ключ: Ключ, який база або застосунок генерує сам: лічильник, ULID, UUID. Він ніколи не змінюється, бо нічого не означає. Його протилежність це природний ключ.
Природний ключ: Унікальна властивість самих даних: email, ISO-код валюти, vendor/package. Зручний, поки не змінюється. Коли змінюється, разом з ним треба переписати всі посилання.
Зовнішній ключ: FOREIGN KEY (company_id) REFERENCES companies (id): значення має існувати в батьківській таблиці або бути NULL. ON DELETE і ON UPDATE кажуть, що робити з дочірніми рядками, коли батьківський зникає чи змінює ключ.
Pivot: Проміжна таблиця звʼязку багато до багатьох, junction table. Два зовнішні ключі, а пара з них зазвичай і є первинним ключем. Laravel називає її з імен двох моделей в однині за абеткою: skill_vacancy.

11 · ПЕРЕВІР СЕБЕ

Питання зі співбесід

Усі питання з SQL →

Третьою таблицею skill_vacancy з двома зовнішніми ключами, vacancy_id і skill_id. Кожен її рядок це одна пара. Первинний ключ (vacancy_id, skill_id) не дасть записати пару двічі, а індекс на skill_id потрібен, щоб шукати вакансії за навичкою.

Залежить від того, чи живе вакансія без компанії. Якщо вакансія без компанії не має сенсу, CASCADE. Якщо її треба лишити в каталозі, SET NULL і nullable колонка. Якщо видалення компанії з вакансіями це помилка, RESTRICT, і код спершу сам закриває або переносить вакансії. Хай яким буде вибір, він має стояти в схемі, бо код обходять консоль і імпорт.

Перевірка зовнішнього ключа шукає вакансії з цим company_id. InnoDB створив індекс під ключем сам, а PostgreSQL ні, тож там кожне видалення перебирає всю таблицю vacancies. В EXPLAIN ANALYZE це видно в рядку Trigger for constraint. Лікується CREATE INDEX на company_id.

Не UUIDv4: в InnoDB випадковий ключ розкидає вставки по кластерному індексу, таблиця й усі вторинні індекси ростуть, вставка сповільнюється в рази. Беріть ULID або UUIDv7, які зростають з часом, і зберігайте компактно (uuid у PostgreSQL, BINARY(16) у MySQL замість char(36)). Інший варіант лишити bigint для звʼязків усередині бази і додати окремий публічний ULID з унікальним індексом лише для URL.

← ПОПЕРЕДНЯNULL і його пастки НАСТУПНА →Нормалізація

ТЕМАІндекси ТЕМАНормалізація ТЕМАТипи даних ТЕМАJOIN ПИТАННЯ · SQLНавіщо зовнішні ключі й що обрати: CASCADE, RESTRICT чи SET NULL? ПИТАННЯ · SQLЧим відрізняються INNER JOIN і LEFT JOIN? ПИТАННЯ · LARAVELЯкі є звʼязки в Eloquent і як обрати між hasMany, belongsToMany і morph? КОНЦЕПЦІЯ · АРХІТЕКТУРАСутність і агрегат

Ключі й звʼязкиKeys and relationships

Рядок, на який можна послатися

Куди лягає новий рядок

База не дасть послатися в нікуди

InnoDB створює його сам, PostgreSQL ні

Таблиця, де рядок це пара

Скільки коштує видалити одну компанію

Як оголосити звʼязок у міграції

Пошук роботи як pivot з даними

Де ключі підводять

Звʼязок без обмеження

Немає індексу під ключем у PostgreSQL

CASCADE там, де дані треба зберегти

REFERENCES в описі колонки в MySQL

UUIDv4 як первинний ключ InnoDB

Питання зі співбесід