Типы ключей в базах данных?

Question

[Webber]

Задача: нужно структурно разделить все сущности бд и понимать кто за что отвечает.

Пытаюсь разобраться в структуре ключей (типах), индексов, связей, но пока путаница в голове.

Пользуюсь программами DataGrip, DBViewer, докой и sql запросами:
1) В DataGrip структурирования нету, только иконки. По ним понятно где ключ, где индекс

spoiler

2) В DBViewer полная структура, при том на 2й вкладке все (связи, индексы, primary) в группе indexes:

spoiler

3) Доке типы индексов:
MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT), а где foreign key если при запросе show indexes он показывается?

4) При запросе show indexes from "table";

spoiler

тут уже написано key_name, а потом index_type - key или index сущность?

Вопросы такие: как все это разбить на конкретные сущности?

Какое общее название индексы или ключи?
ForeignKey это тоже индекс/ключ?
В чем отличие ForeignKey от Reference?
Unique индекс это отдельный индекс или просто флаг unique к обычному индексу?

spoiler

Answer 1

[ayazer]

как все это разбить на конкретные сущности?

см. ниже

Какое общее название индексы или ключи?

это синонимы. исторически сложилось что в скле достаточно много синонимов, т.к. часть из них это стандарт скл, а часть - фишки конкретной субд.

ForeignKey это тоже индекс/ключ?

нет, foreign key != индекс. индекс = вообще отдельная структура которая хранится отдельно от самих данных. foreign key = просто ссылка на другую таблицу, и создание внешнего ключа это по сути создание уникального constraint (таблица1.колонка1, таблица2.колонка2). Потенциально СУБД может сама создавать индекс при создании внешнего ключа (т.к. почти всегда это имеет смысл), но это уже нюанс конкретной реализации

В чем отличие ForeignKey от Reference?

что такое "Reference"? если вы про конструкцию

FOREIGN KEY (ColumnName) REFERENCES TableName(ColumnName)

так это просто ключевое слово. скл первоначально проектировался как язык на котором не-программисты смогут писать запросы к базам, потому он достаточно многословный.

Unique индекс это отдельный индекс или просто флаг unique к обычному индексу?

это обычный индекс + ограничение на уникальность. и это ограничение в будущем можно как удалить, так и добавить (правда с добавлением не все так просто).

Comments

[Webber]

Спасибо.

Primary key это тоже самое что Unique индекс только not null?
CONSTRAINT это просто название ограничения?
Указать unique в create table(column int unique) это тоже самое что CREATE UNIQUE INDEX ?

[ayazer]

AKLZephyr,
1)
если просто - да, только праймари ключ всегда один, а уникальных - может быть много.
если сложно - нет, это все-же разные вещи. праймари ключ это clustered index, т.е. он влияет на то, каким образом данные в таблице физически хранятся на диске. и именно по этой причине можно иметь только 0-1 праймари ключ для таблицы. а nonclustered index - это просто отдельная структура, которая помогает быстрее находить нужную запись.
2) да
3) да

upd: ну и так как в тегах именно майскл, то для
InnoDb - всегда имеет clustered index
myisam - не поддерживает clustered index, а использует heap table (т.е. сваливает все в 1 кучу без какой-либо сортировки)