Зачем нужны внешние ключи прописанные в структуре БД (MySQL) — они действительно там нужны?

Question

[Роми]

Не кидайтесь тапками)) но вопрос вот какого плана:

допустим у меня две таблицы Users и Orders и в Orders у меня user_id - соответствующий id в таблице Users - стандартнейшая ситуация ))

И я могу при создании этих таблиц в базе, в SQL-запросе ли, или в phpMyAdmin, или в миграции, указать что user_id - это FOREIGN KEY - так?

Вопрос такой:

- А зачем это вообще нужно-то? :-)

Ну, в смысле - оно же всё и так будет работать, и без этого. Вся логика же у меня в PHP расположена.

Или с этим ключом будет как-то быстрее?

Или что вообще?

Заранее спасибо за внятный ответ!

Comments

[FanatPHP]

мне всегда казалось что ответ есть в любом тексте про внешние ключи.

Да, функционал внешних ключей можно заменить кодом пхп.
Но зачем, если БД может все сделать за тебя?
Как говорится, лучший код - это его отсутствие

Answer 1

[Akina]

Любая СУБД имеет достаточно мощную систему контроля целостности и непротиворечивости данных. Эта система работает, используя набор правил контроля, описанных в структуре БД, и жёстко следит за тем, чтобы ни одно из правил не было нарушено.

Внешний ключ - это как раз такое правило. Сформулировано оно так: в данном поле таблицы не может храниться значение, которое не присутствует в той таблице, на которую ссылается внешний ключ (rонечно, в зависимости от конкретного текста ссылки внешнего ключа и самого поля тут возможны варианты - например, в этом поле может храниться не только значение, присутствующее в ссылочной таблице, но и NULL). И, имея такое правило, СУБД ни при каких условиях не позволит его нарушить. Любая попытка вставить запись со значением, которого нет в ссылочной таблице, приведёт к ошибке. Любая попытка изменить существующее значение на такое, которого "там" нет - приведёт к ошибке. То же касается и "второй" стороны, СУБД не позволит изменить значение в ссылочной таблице или удалить его (потому что записи в нашей таблице при этом "потеряют" ссылку) - такая попытка корректировки приведёт к ошибке.

Comments

[Rsa97]

То же касается и "второй" стороны, СУБД не позволит изменить значение в ссылочной таблице или удалить его (потому что записи в нашей таблице при этом "потеряют" ссылку) - такая попытка корректировки приведёт к ошибке.

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

[Akina]

Rsa97, я вроде на всё это указал, но без конкретного перечисления, ещё при формулировании правила.

Answer 2

[index0h]

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

На больших объемах данных и нагрузках внешние ключи могут сильно нагружать систему, так же они могу усложнить процесс миграций.

В большинстве случаев FK не нужны, от слова совсем.

Comments

[Akina]

В большинстве случаев FK не нужны, от слова совсем.

Ну насчёт "большинства" - оно как-то сомнительно. Если не поддерживать целостность на уровне сервера - это надо делать со стороны клиента, а ему доверия мало. Сбои опять же - не любое разрушение логической связности, не подкреплённое внешним ключом, обязательно злонамеренное. То есть гораздо выше вероятность, что вместо логически связанной информации в БД обнаружится хаотический хлам.

[index0h]

Akina,

Ну насчёт "большинства" - оно как-то сомнительно

Приведите пример, когда без FK не обойтись.

Если не поддерживать целостность на уровне сервера - это надо делать со стороны клиента, а ему доверия мало.

Следить за целостностью БД со стороны клиента, к которому нет доверия, это шутка такая?

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

Безусловно, с большей частью ошибок можно вполне спокойно бороться транзакциями например.

То есть гораздо выше вероятность, что вместо логически связанной информации в БД обнаружится хаотический хлам.

У вас хвост виляет собакой. Проектирование БД - это труоёмкий и длительный процесс, как разработки, так и поддержки. Если ваша БД - хаотический хлам, то что есть внешние ключи, что нет - она хламом и останется.

[Akina]

Приведите пример, когда без FK не обойтись.

Да сплошь и рядом.

Вот делаете Вы обычный INSERT. Если FK нет - как определить, что словарное поле есть в таблице-словаре? ещё один, проверочный, SELECT, делать, что ли? А если FK имеется - то отсутствующее в словаре значение просто не вставится.

Следить за целостностью БД со стороны клиента, к которому нет доверия, это шутка такая?

А какой ещё остался вариант? только вообще не следить за целостностью... или Вы считаете, что это лучше?

что есть внешние ключи, что нет - она хламом и останется.

Не говорите ерунды. Не соответствующий ограничению хлам в БД просто не попадёт. С точки зрения клиента это будет вообще выглядеть как физическая невозможность.

[index0h]

Если FK нет - как определить, что словарное поле есть в таблице-словаре?

Валидировать данные перед инсертом.

ещё один, проверочный, SELECT, делать, что ли?

Если словарь нельзя ни закэшировать ни вы нести в код - доп. селект вполне норм.

А если FK имеется - то отсутствующее в словаре значение просто не вставится.

Если ваш код отправляет данные на вставку после валидации по хорошему данные должны вставиться.

А какой ещё остался вариант? только вообще не следить за целостностью... или Вы считаете, что это лучше?

Лично я считаю такой вариант - недопустимым. А аргумент - не валидным. Давать доступ к субд клиенту, к которому нет доверия - это путь в никуда. Да, есть green SQL и другие штуки, но тем не менее.

Не говорите ерунды. Не соответствующий ограничению хлам в БД просто не попадёт. С точки зрения клиента это будет вообще выглядеть как физическая невозможность.

Если для вас хламом в БД являются только не целостные записи - это печально.
Что касается вашей уверенности в FK - это дело отключается. Прав на INSERT достаточно, что бы запустить
SET FOREIGN_KEY_CHECKS = 0;
Собсно вся ваша вера в то, что данные останутся целостными базируется на том, что эта команда не будет выполнена.