Почему не используются совпадения первичного ключа / внешнего ключа для соединений?

Насколько мне известно, многие СУБД (например, mysql, postgres, mssql) используют комбинации fk и pk только для ограничения изменений данных, но они редко используются для автоматического выбора столбцов для соединения (например, естественное соединение с имена). Почему это? Если вы уже определили связь между двумя таблицами с pk /fk, почему база данных не может понять, что если я присоединяюсь к этим таблицам, я хочу присоединиться к ним в столбцах pk /fk?

РЕДАКТИРОВАТЬ: немного уточнить это:

Предположим, что у меня есть таблица1 и таблица2. table1 имеет внешний ключ в столбце a, который ссылается на первичный ключ в таблице2, столбец b. Теперь, если я присоединяюсь к этим таблицам, мне нужно будет сделать что-то вроде этого:

  SELECT * FROM table1
JOIN table2 ON table1.a = table2.b
 

Однако я уже определил, используя мои ключи, что table1.a ссылки на table2.b, поэтому мне кажется, что не должно быть сложно заставить систему СУБД автоматически использовать table1.a и table2.b в качестве join, чтобы можно было просто использовать:

  SELECT * FROM table1
AUTO JOIN table2
 

Однако многие СУБД, похоже, не реализуют что-то вроде этого.

42 голоса | спросил Tiddo 16 MaramFri, 16 Mar 2012 04:16:57 +04002012-03-16T04:16:57+04:0004 2012, 04:16:57

12 ответов


29

Во многих случаях существует несколько способов объединения двух таблиц; См. Другие ответы для многих примеров. Конечно, можно сказать, что было бы ошибкой использовать «автоматическое объединение» в этих случаях. Тогда останется только несколько простых случаев, когда он может быть использован.

Однако существует серьезный недостаток! Запросы, которые правильны сегодня, могут стать ошибкой завтра, просто добавив второй FK в ту же таблицу!

Позвольте мне сказать, что снова: добавив столбцы, запросы, которые не используют эти столбцы, могут перейти от «правильного» в «ошибку»!

Это такой кошмар для обслуживания, что любой здравый стиль руководства запретил бы использовать эту функцию. Большинство из них уже запрещают выбирать * по той же причине!

Все это было бы приемлемо, если бы производительность была повышена. Однако это не так.

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

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

ответил 16 MaramFri, 16 Mar 2012 07:07:49 +04002012-03-16T07:07:49+04:0007 2012, 07:07:49
26

Внешний ключ предназначен для ограничить данные. т.е. обеспечить ссылочную целостность. Вот и все. Больше ничего.

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

      table: USA_States
    StateID
    StateName
    
    стол: Отгрузка
    ShipmentID
    Внешний ключ PickupStateID
    Внешний ключ DeliveryStateID
     

    Вы можете захотеть присоединиться на основе состояния срабатывания. Возможно, вы хотите присоединиться к состоянию доставки. Возможно, вы хотите выполнить 2 соединения для обоих! SQL-движок не знает, чего вы хотите.

  2. Вы часто переходите на скалярные значения объединения. Хотя скаляры обычно являются результатом промежуточных вычислений, иногда у вас будет таблица специального назначения с ровно 1 записью. Если движок попытался обнаружить ключ foriegn для соединения ..., это не имело бы смысла, потому что кросс-соединения никогда не совпадают с столбцом.

  3. В некоторых особых случаях вы присоединяетесь к столбцам, где ни один из них не является уникальным. Поэтому наличие PK /FK на этих столбцах невозможно.

  4. Вы можете подумать, что пункты 2 и 3 выше не актуальны, так как ваши вопросы касаются того, когда IS одно отношение PK /FK между таблицами. Однако наличие одиночного PK /FK между таблицами не означает, что вы не можете присоединить другие поля в дополнение к PK /FK. Двигатель sql не знал бы, к каким полям вы хотите присоединиться.

  5. Допустим, у вас есть таблица «USA_States» и 5 других таблиц с FK для состояний. В «пяти» таблицах также есть несколько внешних ключей друг к другу. Должен ли модуль sql автоматически присоединяться к «пяти» таблицам с «USA_States»? Или он должен присоединиться к «пятерке» друг к другу? И то и другое? Вы можете настроить отношения так, чтобы движок sql вводил бесконечный цикл, пытаясь объединить все вместе. В этой ситуации невозможно заставить SQL-сервер угадать, что вы хотите.

Вкратце: PK /FK не имеет ничего общего с объединениями таблиц. Это отдельные несвязанные вещи. Просто случайно, что вы часто присоединяетесь к колонкам PK /FK.

Вы хотите, чтобы движок sql догадался, если это полное, левое, правое или внутреннее соединение? Я так не думаю. Хотя это, возможно, было бы меньшим грехом, чем угадывание столбцов для присоединения.

ответил Lord Tydus 16 MaramFri, 16 Mar 2012 06:13:58 +04002012-03-16T06:13:58+04:0006 2012, 06:13:58
10
  

понятие «соединяемость». Отношения r1 и r2 являются совместимыми, если   и только если атрибуты с тем же именем имеют один и тот же тип ... это   концепция применяется не только к объединению как такому, но и к различным другим   операции [такие как объединение].

SQL и реляционная теория: как написать точный код SQL по дате CJ

В стандартном SQL уже есть такая функция, известная как NATURAL JOIN , и была реализована в mySQL.

Хотя ваше предложение не так достойно, оно кажется разумным. С SQL Server (который не поддерживает поддержку NATURAL JOIN ), я использую SQL-запрос в Management Studio: при написании INNER JOIN его InteliSense предлагает ON предложения на основе как общих имен атрибутов, так и внешние ключи, и я считаю это очень полезным. У меня нет большого желания видеть новый (стандартный) тип соединения SQL для этого, однако.

ответил onedaywhen 16 MarpmFri, 16 Mar 2012 17:15:48 +04002012-03-16T17:15:48+04:0005 2012, 17:15:48
8

SQL пришел первым!

Внешние ключи и ограничения внешнего ключа пришли позже и по сути являются оптимизацией для приложений типа «транзакция».

Реляционные базы данных изначально были задуманы как метод применения комплексных запросов к наборам данных таким образом, чтобы математически доказывался с использованием реляционной алгебры. И.Е. для заданного набора данных и заданного запроса всегда есть один правильный ответ.

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

Однако орган стандартов ANSI для всех своих противоречивых целей и политики поставщиков всегда стремился сохранить «математически доказуемые» свойства SQL.

Если вы позволили базе данных вывести свойства соединения из «скрытых» данных внешнего ключа, вы потеряете это свойство (рассмотрите двусмысленность, если определено более одного набора внешних ключей).

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

ответил James Anderson 16 MaramFri, 16 Mar 2012 06:08:47 +04002012-03-16T06:08:47+04:0006 2012, 06:08:47
6

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

  • Вы можете использовать декартово произведение двух таблиц или их частей для какой-либо цели.
  • Могут быть другие поля, по которым вы можете присоединиться к другой цели.
  • Если вы присоединяетесь к трем или более таблицам, одна из таблиц может быть связана с двумя или более таблицами. В этом случае в запросе может быть только одно из возможных отношений FK.
ответил BillThor 16 MaramFri, 16 Mar 2012 05:16:22 +04002012-03-16T05:16:22+04:0005 2012, 05:16:22
6

Вы можете работать с ложным предположением. Вы говорите «насколько можете узнать», но не даете никаких эмпирических или доказательных доказательств. Если pk или fk являются лучшим индексом для запроса, он будет использоваться. Я не знаю, почему вы это видите, но я думаю, что это плохо сформированные запросы.


Теперь отредактируйте, что вопрос был полностью переписан: Случай, который вы описываете, будет только для очень небольшого набора запросов. Что делать, если есть 12 столов? Что делать, если нет FK ... Даже если бы было соединение по умолчанию, я все равно всегда указывал бы соединение только для удобства чтения. (Я не хочу смотреть данные, а затем пытаюсь выяснить, к чему присоединяется)

Некоторые инструменты запроса фактически выполняют автосоединение для вас, а затем позволяют удалить или изменить соединение. Я думаю, что Query Builder от MS Access делает это.

Наконец, в стандарте ANSII указано, что соединение должно быть указано. Это достаточная причина, чтобы не допускать этого.

ответил Morons 16 MaramFri, 16 Mar 2012 04:24:23 +04002012-03-16T04:24:23+04:0004 2012, 04:24:23
2

Существует много причин, по которым база данных не может безопасно это делать, включая тот факт, что добавление /удаление внешних ключей изменяет значение предварительно написанных запросов, включая запросы в исходном коде приложение. В большинстве баз данных также нет хорошего набора внешних ключей, которые охватывают все возможные объединения, которые вы, вероятно, захотите сделать. Кроме того, для улучшения или для достоинства внешние ключи часто удаляются для ускорения систем и не могут использоваться для таблиц, загруженных в «неправильный» порядок из файла.

Однако нет причин, по которым дизайн запроса инструмент или текстовый редактор не может автоматически завершить соединение с помощью внешних ключей так же, как они дают вам intellisense по имени столбца. Вы можете отредактировать запрос, если инструмент получил это неправильно и сохранил полностью определенный запрос. Такой инструмент также может с пользой использовать соглашение об именах столбцов внешнего ключа с помощью имени таблицы «parent »и столбцов с тем же именем как в родительской, так и в дочерней таблице и т. Д.

(Моя жена все еще не может понять разницу между Management Studio и Sql Server и рассказывает о запуске SQL-сервера, когда она запускает студию управления!)

ответил Ian Ringrose 16 MarpmFri, 16 Mar 2012 13:58:18 +04002012-03-16T13:58:18+04:0001 2012, 13:58:18
2

Естественное объединение «автоматически» соединяется с равенством общих столбцов, но вы должны написать только это, если это то, что вы хотите на основе значений таблиц и вашего результата. Нет «автоматически», зная, как две таблицы «должны» быть объединены или каким-либо другим образом любая таблица «должна» появляться в запросе. Нам не нужно знать ограничения для запроса. Их присутствие просто означает, что входы могут быть ограничены, и, следовательно, выход может быть слишком. Вы можете определить какой-то оператор join_on_fk_to_pk, который «автоматически» присоединяется к объявленным ограничениям; но если вы хотите, чтобы значение запроса оставалось неизменным, если изменялись только ограничения, но не значения в таблице, вам придется изменить этот запрос на not на использование новых объявленных констант. Просто давая запрос, который вы хотите использовать join & amp; условия уже оставляют значение одинаковым, несмотря на любые изменения ограничений .

Какие ограничения удерживают (включая PK, FK, UNIQUE и CHECK), не влияют на то, что означает таблицы. Разумеется, если значения таблицы изменяются, то могут измениться ограничения. Но если изменения ограничений не означают, что запросы должны измениться.

Не нужно знать ограничения для запроса. Знание о ограничениях означает, что мы можем использовать дополнительные выражения, которые без удерживания ограничения не возвращают тот же ответ. Например, ожидая через UNIQUE, что таблица имеет одну строку, поэтому мы можем использовать ее как скаляр. Эти запросы могут прерываться, если ограничение было принято, но не объявлено. Но объявление ограничения, которое запрос не предполагал, не может его сломать.

Есть ли какое-либо эмпирическое правило для создания SQL-запроса из удобочитаемого описания?

ответил philipxy 15 +03002017-10-15T00:34:00+03:00312017bEurope/MoscowSun, 15 Oct 2017 00:34:00 +0300 2017, 00:34:00
0

Причина в том, что есть ЯЗЫК, а затем есть основные принципы. Язык редок и недостаток многих функций, которые вы ожидаете увидеть на языке общего назначения. Это просто отличная функция, которая не добавлена ​​в язык и, вероятно, не будет. Это не мертвый язык, поэтому есть надежда, но я бы не был оптимистом.

Как указывали другие, некоторые реализации используют расширение, в котором join (column) объединяет две таблицы на основе общего имени столбца, что несколько похоже. Но он не широко читается и не полагается на то, что между таблицами существует взаимосвязь.

Нет фундаментального препятствия для «внутренней таблицы соединений на PKFK», где PKFK является ключевым словом, означающим «отношение внешнего ключа, определенное между двумя таблицами», могут быть проблемы с несколькими fk в одну и ту же таблицу, но это может просто вызвать ошибка. Вопрос заключается в том, что люди, разрабатывающие язык, считают, что a) хорошая идея и б) лучше работать, чем некоторые другие изменения языка ...

ответил jmoreno 16 MaramFri, 16 Mar 2012 07:56:32 +04002012-03-16T07:56:32+04:0007 2012, 07:56:32
0

Если опускать предложение ON, как предполагается, следует за полями на основе ссылочной целостности, как вы будете делать декартово произведение?

Изменить: использование AUTO Преимущества этого - немного меньше печатать, и вам не нужно знать, как они объединены или помнить сложное соединение. Если отношение изменяется, оно обрабатывается автоматически, но это редко происходит, за исключением раннего развития.

Теперь вам нужно решить, все ли ваши соединения AUTO задерживаются во время изменения отношения, чтобы соответствовать намерению вашего оператора select.

ответил JeffO 16 MaramFri, 16 Mar 2012 07:42:00 +04002012-03-16T07:42:00+04:0007 2012, 07:42:00
-1
  

почему база данных не может понять, что если я присоединяюсь к этим таблицам, я хочу присоединиться к ним в столбцах pk /fk?

Части причины:

1 - теоретически вы можете присоединять таблицы к произвольным столбцам из двух таблиц. Хотя это не обычная практика, она действительна. Помните, что SQL похож на язык программирования, он не понимает, какая информация находится внутри столбцов курса и имен, для SQL, в этом не означает многого.

2 - Существуют разные типы объединений (слева, righ, internal). Внутренние соединения - только 1 из них.

3 - стандарт SQL может руководствоваться принципом языка более низкого уровня, который позволяет диалектам более высокого уровня формировать интеллект, используя его. Сравнение несколько яснее, если вы думаете о языке 4-го поколения и языке третьего поколения. Фактически, один инструмент, который я использовал, IEF, позволил вам написать что-то вроде этого:

  Клиент ReadEach
Где заказ клиентов и заказчик живет в «Калифорнии»
и OrderValue> 100,00
 

Вкратце ваше предложение интересное и может быть реализовано либо как часть стандарта, либо как хранимая процедура (по умолчанию он будет включать Inner Join).

ответил NoChance 16 MarpmFri, 16 Mar 2012 13:23:43 +04002012-03-16T13:23:43+04:0001 2012, 13:23:43
-10

Тиддо, я считаю, что вы совершенно правы, SQL на эту тему довольно глупо , и я помню, что думал о том же, что и вы, о внешних ключах, изучая SQL примерно десять лет назад.

Хорошо, учитывая, что я в конце концов должен был сдать этот экзамен; и передать его, мне пришлось отпустить . SQL больше похожа на поезд, чем кто-либо, возможно, признает, что его путь стандартизации является полной катастрофой, а некоторые реализации угрожающе совершенствуются . Тем не менее, это очень удобно, в общем. (Я не K /V luddite)

Внешние ключи, тогда ... не все это удобно. Это важная концепция в реляционной модели , но функция SQL с тем же именем плохо сравнивается.

Скажите прямо: не использовать эту функцию SQL, называемую Foreign Key вообще , пока вы не столкнетесь с какой-либо большой системой с проблемами производительности. Явно сообщая движку , какое поле является внешним ключом, а не - это only , используемое для индексирования, и оно невидимо для пользователя db.

Это вводит в заблуждение?
Да.

Они собираются сделать его более мощным сейчас, после того, как 30 лет людей вводят в заблуждение?
Не случайно.

Полностью игнорировать внешние ключи до тех пор, пока вам не понадобится ... исправлено SQL для меня?
Да!

И почему все это произошло в первую очередь?
Ну, функция, которую мы вызываем внешние ключи была добавлена ​​позже, SQL; SQL - это стандарт, который развивается со временем, снизу вверх. Продавцы реализовали смехотворные функции, в то время как стандартные тела сталкивались.

Внешние ключи, как сказано, где предназначены только для индексирования, и не было никакой конструкции JOIN. (присоединяется, когда выполняется с запросами SELECT , JOIN запросы довольно свежие и предназначены только для псевдонима SELECT ). Вероятно, хотя это вызывает этот флаг индексирования FOREIGN KEY , был умным наименованием-хаком над концепциями теории реляционных db.

ответил 16 MaramFri, 16 Mar 2012 05:45:27 +04002012-03-16T05:45:27+04:0005 2012, 05:45:27

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132