Отношение "много ко многим" между одной и той же таблицей в MS SQL Server Compact (.NET Framework 3.5)

[AlexRock]

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

Таблицы-то я создал, а при создании отношений между ними начали вылезать ошибки. Я пробовал два варианта.

1 вариант. Одно отношение с двумя парами соотносящихся столбцов - см. рисуноки ниже и получившуюся ошибку при попытке добавить отношение (в ошибках - вся соль этой темы):

(далее во всех таблицах столбцы Id - первичные ключи)

схема участка БД:



свойства таблицы TankMedley:



создание отношения:



Тут сказано, что у таблицы, на которую ссылаются (у меня это таблица Herbicide), должен быть первичный ключ (или какой-то кандидат). Но она имеет первичный ключ!

Ладно, тогда попробую другой вариант.

2 вариант. Два отношения, каждое - с одной парой соотносящихся столбцов. Первое отношение добавляется нормально, а второе выдаёт ошибку (см. ниже):

схема участка БД:



создание первого отношения:



создание второго отношения:



Тут сказано, что ссылочное отношение приведёт к созданию циклической ссылки, что непозволимо. Но ведь у меня нет здесь цикла! Цикл бы был, если бы я в разные стороны ссылался в каждом отношении, а тут у меня в одну сторону - к одной таблице - все отношения идут, поэтому при удалении любой строки из таблицы "выше по иерархии" (здесь это Herbicide) у меня удалятся (каскадно) только строки в таблице связей TankMedley, а при удалении любой строки в TankMedley, из Herbicide ничего удаляться не будет. Т. е. цикла нет!

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

Проверили в Access - там можно всё это сделать при помощи всего двух таблиц, как я и хотел вначале, но там скрыта от пользователя вся кухня создания отношений и правил удаления/обновления (или мы не знаем, где это делается).

Вобщем, вопрос - это я что-то неправильно делаю, или это MS SQL Server Compact такой ограниченный? Неужели придётся делать таблицу-копию, чтобы достиг желаемого результата?

[Richard Ferlow]

Я помню делал как-то так

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

Таким образом для каждого гербицида создается столько записей, сколько он может раз взаимодействовать.

уникальный id записи - чтобы в случае чего к конкретной записи обратиться

[AlexRock]

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

Вот, хотел бы услышать мнения людей, которые создавали такие отношения и связи - как вы разруливали эту проблему?

[AlexRock]

Вот простой пример, где наглядно можно объянсить получившуюся проблему.

Есть одна таблица Гербициды и есть другая ТаблицаСвязей, которая связывает Гербициды саму с собой отношением "много ко многим". Пусть ТаблицаСвязей заполнена так:

Id | G1Id | G2Id - айдишники записей в ТаблицеСвязей и двух гербицидов, могущих взаимодействовать.

1 | 1 | 2
2 | 1 | 3
3 | 2 | 3

При удалении из таблицы Гербициды гербицида с айдишником 2 должны удалиться записи 1 и 3 (из столбцов G1Id и G2Id, соответственно) из ТаблицыСвязей по каскадному правилу, но MS SQL Server Compact не даёт сделать это - он может задать каскадное правило только для одного столбца (G1Id или G2Id). Данные по совпадению во втором столбце придётся удалять "вручную" - писать запрос на удаление. Т. е. нельзя добиться автоматического удаления из обоих столбцов.

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

Похоже, что это недоработки этих СУБД... или мы просто не знаем, как ими пользоваться, чтобы добиться нужных результатов.

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


Вобщем, у кого ещё есть мысли - буду рад узнать.

[Алексей Н]

надо описать таблицу Herbicide дважды, с другим алиасом второй раз.

[AlexRock]

[BorPas]

да нужно не две таблицы, а два алиаса на одну таблицу в запросе написать

пример на SQL

[Алексей Н]

[Serge]

[AlexRock]

Про псевдонимы (алиасы) я что-то не помню - почитаю. Спасибо.

[AlexRock]

[Алексей Н]

Не в том дело. Просто бывает так что ставят удаление не подумав (классная фича!), а потом выясняется что оно "тут нужно, а тут не нужно, поэтому надо убрать".