Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MS SQL Server > Количество связанных записей из других таблиц

Автор: Gwire 5.2.2012, 04:46

Доброго здоровья. Я проектирую базу, которая будет еще в дальнейшем изменяться и расти, по мере поступления требований от заказчика. (Может, требования и не поступят, но лучше на это заложиться) Каждая таблица имеет поле [id] которое primary key. Идея моей проблемы такова. Написать функцию (назовем её [Get_Using_Count]), которая     получает: имя таблицы и id записи     возвращает: кол-во применений этого id в связанных таблицах. Что это значит. Например: Есть таблицы [Накладные], [Товары к накладным] и [Чеки оплаты] Случается что накладные создаются по ошибке и не имеют связанных "Товаров к накладным" и "Чеков оплаты" То есть ни одна запись в этих таблицах не ссылается на "нашу" накладную (у них есть поле накладная_id). Выполнив запрос "SELECT *, Get_Using_Count('dbo.Накладные', [id]) FROM [Накладные]", можно смело удалять записи у которых кол-во связанных записей 0 Следующий код формирует уже готовые запросы на выборку для каждой связанной таблицы с указанной. Код DECLARE @ID bigint = 200 DECLARE @TableName varchar(50) = 'dbo.Накладные' SELECT     '(SELECT COUNT(id) FROM ['+     SCHEMA_NAME(P.[schema_id]) +'].['+     OBJECT_NAME(P.[object_id]) +'] WHERE ['+     (SELECT name FROM [sys].[columns]        WHERE ([object_id]=F.[parent_object_id])           AND ([column_id]=F.[parent_column_id])     ) +']='+ CAST(@ID as varchar) +')+'   FROM [sys].[foreign_key_columns] AS F,        [sys].[objects] AS P   WHERE referenced_object_id = OBJECT_ID(@TableName,'U')     AND (P.[object_id] = F.[parent_object_id]) На ваших базах она тоже отработает ничего не переделывая (только тип @ID, возможно) Натравив на нее CURSOR я намеревался собрать их в одну строку и выполнить Код DECLARE TableList CURSOR FOR SELECT ...-- Из предыдущего блока DECLARE @T nvarchar(100) DECLARE @S nvarchar(4000) = 'SELECT ' OPEN TableList FETCH NEXT FROM TableList INTO @T WHILE @@FETCH_STATUS = 0 BEGIN     SET @S = @S + @T     FETCH NEXT FROM TableList INTO @T END CLOSE TableList DEALLOCATE TableList EXEC (@S + '0')  --*1* Но вот не задача - я не могу выполнить EXEC, так чтобы он мне вернул результат не на экран а в переменную. Допустим её можно решить через еще один CURSOR. Заменить строчку *1* на Код DECLARE @Res int EXEC ('DECLARE TempCursor CURSOR FOR '+ @S + '0')  --*2* OPEN TempCursor FETCH NEXT FROM TempCursor INTO @Res CLOSE TempCursor DEALLOCATE TempCursor SELECT @Res Но возникает вторая проблема которую я не смог побороть Это: Сообщение 443, уровень 16, состояние 14, процедура Get_Using_Count, строка 32 Недопустимое использование оператора "EXECUTE STRING", оказывающего побочное действие, в функции. Возникает в строчке *2* Вопрос: Как обойти этот запрет? Или как решить задачу другим методом?

Автор: tzirechnoy 5.2.2012, 10:33

То есть, если Вам вдруг захочется, допустим, создать к накладным список необходимых подписей, подключить его через классическое многие-ко-многим и прописать дефолт -- то вся Ваша изящная схема успешно накернится. Или, допустим, не Вам, а следующему программисту, ковыряющему эту опердень. Он будет рад, вероятно. В общем, не выпендривайтесь. Перечислите список зависимых таблиц и полей явно.  Если очень хочется сделать одну абстракцыю в БД, отвечающую за это -- вынесите во view. Впрочем, по-моему, дажэ в этом смысла нет: оно, скорее всего, нужно строго в одном месте -- потому делайте просто запросом, без функцый и прочих наворотов.

Автор: Gwire 5.2.2012, 15:46

Цитата(tzirechnoy @  5.2.2012,  10:33 ) через классическое многие-ко-многим Я никогда не связываю таблицы этим методом. Это плохая практика. Если такие ситуации возникают, создается таблица для избавления от "порочных" связей К примеру: Две таблицы [Пользователи] и [Привилегии] видим отношение многие-ко-многим. Создаем таблицу [Назначенные_Привилегии] в которой 3 поля [id], [пользователь_id] и [привилегия_id]. В моем случаи "классическое многие-ко-многим" совсем не классическое . Цитата(tzirechnoy @  5.2.2012,  10:33 )  не Вам, а следующему программисту, ковыряющему эту опердень. Другого программиста не будет. У меня заказчик родной дядя.  А если и будет, то ему просто придется немного подумать и не создавать связи многие-ко-многим

Автор: Zloxa 5.2.2012, 16:10

Цитата(Gwire @  5.2.2012,  04:46 ) Написать функцию (назовем её [Get_Using_Count]), которая     получает: имя таблицы и id записи     возвращает: кол-во применений этого id в связанных таблицах. Цитата(Gwire @  5.2.2012,  04:46 ) можно смело удалять записи у которых кол-во связанных записей 0 Дайте угадаю, вы накрутили  foreign keys с каскадными операциями, а теперь пытаетесь реализовать их первородную функцию?  Добавлено @ 16:11 Цитата(Gwire @  5.2.2012,  15:46 ) Я никогда не связываю таблицы этим методом. Это плохая практика. Отношение многие ко многим - самая чт они наесть обычная практика. В отличии от каскадных операций на внешних ключах. Добавлено @ 16:12 Цитата(Gwire @  5.2.2012,  15:46 ) В моем случаи "классическое многие-ко-многим" совсем не классическое . Вы описали что ни наесть классическое многие-ко-многим.

Автор: Gwire 5.2.2012, 18:06

Цитата(Zloxa @  5.2.2012,  16:10 ) Дайте угадаю, вы накрутили  foreign keys с каскадными операциями, а теперь пытаетесь реализовать их первородную функцию? Нет. Обычный внешний ключ, не позволяющий удалять если есть связи. Логика такова: если запись попала в базу и к ней были добавлены связанные записи, она не может быть удалена. Например: Таблица [Список_Товаров]. Как я могу позволить удалить какой-то товар, если есть накладные которые используют этот товар. А представляете, что будет если удалить товар каскадно. Мне эта функция нужна для информативности. Что бы оператор понимал какие записи (накладные, товары, клиенты, адреса ...) являются "мусором". Для чистки базы от этого "мусора" (вручную. Возможно какие-то записи будут добавлены наперед, и об этом знает только оператор). Цитата(Zloxa @  5.2.2012,  16:10 ) В отличии от каскадных операций на внешних ключах. Как я уже сказал я не использую каскадные операций. При удалении записи, запись не удаляется физически (если есть связи), а устанавливается поле [enable] = 0. Цитата(Zloxa @  5.2.2012,  16:10 ) Вы описали что ни наесть классическое многие-ко-многим Логически они существуют, как в моем примере. Но в базе нет таблиц связанных между собой методом "многие-ко-многим". Как я уже говорил "Это плохая практика". От этих моментов нужно избавляться еще на этапе проектирования базы.

Автор: Zloxa 5.2.2012, 20:15

Цитата(Gwire @  5.2.2012,  18:06 ) Как я уже говорил "Это плохая практика". От этих моментов нужно избавляться еще на этапе проектирования базы.    Вы бредите. Цитата(Gwire @  5.2.2012,  18:06 ) Логически они существуют, как в моем примере. Ваш пример описал физическую реализацию связи многие ко многим. Она классически реализуется физически через третью таблицу.  И это нормальная парктика. Вы себе противоречите. Цитата(Gwire @  5.2.2012,  18:06 )  Как я могу позволить удалить какой-то товар, если есть накладные которые используют этот товар.  Для того, чтобы вы этого не смогли и устанавливаются внешние ключи, зачем дублировать их функционал собственным велосипедом? (других версий, оправдывающих такое желание, кроме касскадов, я высосать из пальца  не смог ) Тем более что вы врядли сможете его реализовать более эффективно нежели это уже сделано на стороне СУБД. Вы не думали над тем, что в процессе вычисления вашей функции, могут измениться данные, повлиявшие на ее результат? Например вы удаляете товар: функция уже просмотрела таблицу накладных, стала смотреть в таблице остатков, а в это время другой пользователь создал накладную, которая приходует удаляемый вами товар, а к тому моменту, как функция закончит свою работу, вполне возможно, товар уже будет проведен и по остаткам. Как вы полагали избегать такой ситуации? Захватывать в монопольный доступ все объекты базы, которые используют товар? Проверка по внешним ключам, эту задачу решает куда меньшим количеством ресурсов. Пытаемся удалить запись, если попытка обламывается с ошибкой по нарушению внешнего ключа, размечаем запись как не активную - и все.

Автор: Gwire 6.2.2012, 05:11

Zloxa, у меня складывается впечатление, что Вы читаете посты через слово или не особо разбираетесь в разнице между связями таблица-таблица и сущность-сущность. Вы о какой связи думали когда писали: Цитата(Zloxa @  5.2.2012,  20:15 ) Цитата(Gwire @  5.2.2012,  18:06 ) Как я уже говорил "Это плохая практика". От этих моментов нужно избавляться еще на этапе проектирования базы.     Вы бредите. По всей видимости это была связь сущность-сущность. Потому как ваша реакция, оправдана только в этом случае. Действительно, невозможно избавится от связей многие-ко-многим между сущностями. Но таких связей нужно избегать между таблицами, путем добавления третей, связующей, таблицы.  Цитата(Zloxa @  5.2.2012,  20:15 ) Она классически реализуется физически через третью таблицу Здесь, Вы уже говорите о таблицах, где я с вами солидарен, тем более, что я всегда говорил о таблицах. Связь между не уникальным полем одной таблицы и не уникальным полем другой таблицы - это простая формулировка связи многие-ко-многим между таблицами. Именно от этих связей лучше избавляться. Именно они являются плохой практикой. А то что, связь между сущностями многие-ко-многим -  Цитата(Zloxa @  5.2.2012,  16:10 ) что ни наесть классическое ни кто не спорит. Цитата(Zloxa @  5.2.2012,  20:15 ) Вы не думали над тем, что в процессе вычисления вашей функции, могут измениться данные, повлиявшие на ее результат? Например вы удаляете товар: функция уже просмотрела таблицу накладных, стала смотреть в таблице остатков, а в это время другой пользователь создал накладную, которая приходует удаляемый вами товар, а к тому моменту, как функция закончит свою работу, вполне возможно, товар уже будет проведен и по остаткам. Во первых: один пользователь не может удалять накладные другого, если не имеет определенных привилегий.   А тот кто имеет привилегий будет это делать в конце рабочего дня (иначе от всех получит по шее). Во вторых: я не собираюсь с помощью нее удалять записи. Цитата(Gwire @  5.2.2012,  18:06 ) Мне эта функция нужна для информативности. Что бы оператор понимал какие записи (накладные, товары, клиенты, адреса ...) являются "мусором". Для чистки базы от этого "мусора" (вручную. Возможно какие-то записи будут добавлены наперед, и об этом знает только оператор). Имелось ввиду, что оператор принимает решение, что является "мусором". Программа, со своей стороны, просто подсвечивает несвязанные записи. И "оператор" имеется ввиду любой пользователь программы (с любыми привилегиями). Цитата(Zloxa @  5.2.2012,  20:15 ) Пытаемся удалить запись, если попытка обламывается с ошибкой по нарушению внешнего ключа, размечаем запись как не активную Так и реализовано. Но этот метод не подходит для того, что бы просто посмотреть "пустая" запись или нет.

Автор: Akina 6.2.2012, 09:45

Цитата(Gwire @  5.2.2012,  05:46 ) базу, которая будет еще в дальнейшем изменяться и расти Уважаемый Gwire, сравнение Ваших постингов на форуме с постингами Zloxa порождает навязчивое желание предложить Вам посчитать своё мнение ошибочным и прислушаться к его советам... и уж тем более не пытаться обвинять его в отсутствии знаний в тех областях, где он явно на две головы выше Вас... Впрочем, моё мнение Вы тоже имеете право проигнорировать.

Автор: Zloxa 6.2.2012, 09:55

Цитата(Gwire @  6.2.2012,  05:11 ) Связь между не уникальным полем одной таблицы и не уникальным полем другой таблицы - это простая формулировка связи многие-ко-многим между таблицами. Именно от этих связей лучше избавляться. Именно они являются плохой практикой. Вы заблуждаетесь, когда называете "Связь между не уникальным полем одной таблицы и не уникальным полем другой таблицы" отношением многие-ко-многим. На основании этого заблуждения вы заявили, что связи многие-ко-многим - плохая практика, которую следует избегать. Я оспариваю исключительно этот тезис. То что под связью многие-ко-многим вы имеете в виду вовсе не связь многие-ко-многим, нисколь не нивелирует абсурдности  вашего заявления.   Если это ваше, столь же уверенное, сколь и абсурдное зявление прочтет новичек, это, определенно, может сбить его с толку, пустить по ложному пути развития. Лишь по этой причине я его так настойчиво оспариваю.  Цитата(Gwire @  6.2.2012,  05:11 ) Программа, со своей стороны, просто подсвечивает несвязанные записи Вы еще и в запросе собираетесь эту функцию использовать?   Что ж. Дело ваше. Лоб ваш, не мне его жалеть.    Впрочем, может статься, вы его и не расшибете... в этот раз

Автор: Gwire 6.2.2012, 14:48

Zloxa, прошу прощения. MS SQL действительно не умеет создавать настоящие связи многие-ко-многим между двумя таблицами. Моя ошибка не проверил. Потому как был уверен что, все СУБД это умеют (точно могут FoxPro для dos, Access 2003, и Oracle не помню версию). Теперь понятно почему у вас с Akina, мои реплики вызвали негодование. MS SQL убрав возможность создавать такие связи не могла убрать самого понятия "многие-ко-многим", и они его перефразировали. Цитата(http://www.codenet.ru/db/oracle/oraclepr_04.php) Отношение многие-ко-многим представляет собой отношение при котором записям родительской таблицы соответствуют записи дочерней таблицы, а ряду записей дочерней таблицы соответствуют записи в родительской таблицы (рис.13). Использование такого типа отношений крайне ограничено, не только из-за того, что некоторые БД его вообще не поддерживают на уровне индексов и ссылочной целостности, но и потому, что практически любое отношение многие-ко-многим может быть заменено одним или более отношением один-ко-многим (посмотрите на пример на рис.13. и так не когда не делайте). По всей видимости tzirechnoy тоже думал о тех-же "многие-ко-многим" что и я. Цитата(tzirechnoy @  5.2.2012,  10:33 ) То есть, если Вам вдруг захочется, допустим, создать к накладным список необходимых подписей, подключить его через классическое многие-ко-многим Теперь не вижу проблемы. Считаю вопрос со связями решенным. Остается "первородный" вопрос:   Можно ли выполнить запрос сохраненный в строке из функции?   Если "Да" - подскажите как. Точку зрения tzirechnoy я понял. Если совсем ничего не найдется - реализую обычным перечисление.

Автор: Akina 6.2.2012, 15:03

Цитата(Gwire @  6.2.2012,  15:48 ) был уверен что, все СУБД это умеют Те СУБД, которые это умеют, на самом деле просто умеют создать эту самую дополнительную промежуточную связующую таблицу и далеко спрятать её от конечного пользователя.

Автор: Gwire 6.2.2012, 15:23

Akina, так Вы согласны, что мое мнение не было ошибочным. И что Вы с Zloxa просто могли сказать, что MS SQL не поддерживает прямые связи такого типа (если конечно знали что бывает по другому).

Автор: Akina 6.2.2012, 16:31

Gwire, да я вообще дилетант... но твёрдо знаю, что много-ко-много следует реализовывать через связующую таблицу, даже когда мне предлагают этот сервис в готовой форме (Аксесс-2003 не умеет, а 2007 умеет - но ничто не заставит меня этой возможностью воспользоваться). Ибо "всё, что ты не укажешь как именно делать, имеет право делаться как угодно" - а мне такого добра даром не надо.

Автор: Zloxa 6.2.2012, 17:48

Цитата(Gwire @  6.2.2012,  14:48 ) Oracle не помню версию Постарайтесь вспомнить. Мне очень интересно.   8,9,10,11 - не умеют создавать fk к полю, не имеющему ограничение уникальности.    Цитата(Gwire @  6.2.2012,  14:48 ) По всей видимости tzirechnoy тоже думал о тех-же "многие-ко-многим" что и я. По всей видимости вы не поняли о чем говорил tzirechnoy. Впрочем, я тоже не уверен что правильно его понял. На сколько я понял, он  он имеет в виду возможность расширения схемы таким образом, что ваша проверка всегда будет возвращать наличие связей. В качестве примера он привел расширение атрибутов накладной списком утверждающих ее лиц, который заполняется автоматически при создании этой накладной. Ваша проверка тогда станет возвращать наличие связанных записей, при этом накладная все равно может попадать под критерий удаления.  Цитата(Gwire @  6.2.2012,  15:23 ) И что Вы с Zloxa просто могли сказать, что MS SQL не поддерживает прямые связи такого типа Даже не смотря на то, что, возможно, некие СУБД позволяют создавать связь по не уникальному полю, утверждение что отношение многие-ко-многим порочная практика, которой следует избегать, само по себе, остается абсурдным.

Автор: Gwire 7.2.2012, 00:38

Zloxa, читайте внимательнее

Автор: tzirechnoy 7.2.2012, 12:58

Цитата MS SQL убрав возможность создавать Какой MS SQL? То есть я, конечно, вижу, что Вы на нём пишэте -- но у меня про него ни слова вообще. Я MS SQL видел один раз в жызни -- констатировал, что восстановить бэкап практически невозможно, проще сделать всё заново. Классическая связь многие-ко-многим в реляцыонной алгебре реализуется через промежуточное отношэние, подтверждаю.   Кстати, я с трудом понял, что Вы понимаете под словом многие-ко-многим -- по-моему, это вообще не связь, а две связи один-ко-многим. Не важно, на самом деле, и мне это не интересно. Не ввязывался в спор Zloxa потому, что во-первых он говорил вполне правильные вещи, во-вторых это всё не имеет никакого отношэния к теме. Точно такжэ можно добавить связь один-к-одному со значением по умолчанию и соответствующим foreign key -- ровно с теми жэ проблемами. Ваша ссылка на то, что Вы делаете БД дяде, потому можно проектировать всякую чушь, меня особенно умилила, кстати.

Автор: Zloxa 7.2.2012, 14:06

Цитата(Akina @  6.2.2012,  16:31 ) даже когда мне предлагают этот сервис в готовой форме (Аксесс-2003 не умеет, а 2007 умеет - но ничто не заставит меня этой возможностью воспользоваться) Хотел было поудивляться чудесам от Акцесс 2007, но он меня  удивлять категорически отказался.    Создать связь без контроля целостности он не отказывается. Но эта связь ни о чем не говорит. Это просто полоска на диаграмме и выражение join по умолчанию в мастере запросов. Так что пользоваться - можно, ничего страшного он не творит

Автор: Akina 7.2.2012, 17:06

Цитата(Zloxa @  7.2.2012,  15:06 ) пользоваться - можно, ничего страшного он не творит     Да, можно, конечно... когда чётко понимаешь, что это и как. Проблемы возникнут, если этой фичей бездумно, просто потому что есть и вроде бы удобно, воспользуется тот, кто плохо понимает суть происходящего. Да и понимающий, приняв что-то подобное на сопровождение, тоже вряд ли заколотится от счастья.  Уж лучше пусть его будет однозначно и руками... так спокойнее.

Автор: Gwire 7.2.2012, 18:24

Цитата(tzirechnoy @  7.2.2012,  12:58 ) проектировать всякую чушь Что конкретно Вам кажется чушью? Функция не будет применяться ко всему что попало.  Она нужна для: Используется ли штрих-код, Создавал ли что то пользователь. Использовался ли товар. А вообще ладно. Забейте. Похоже желающих помочь с EXEC нет.

Автор: tzirechnoy 8.2.2012, 12:42

Цитата Что конкретно Вам кажется чушью? То, что вполне простой реляцыонный конструкт Вы пытаетесь выражать через какие-то императивные действия с потрохами одного конкретного RDBMS-продукта. То, что Вы усложняете код и навешываете точки отказа и внутренние зависимости под видом генерализацыи одного частного, редкоиспользуемого случая.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)