Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Привет.
Подскажите пожалуйста, как с помощью системных таблиц (т.е. с минимум ручного забивания информации) узнать есть ли на конкретную запись конкретной таблицы - внешние ключи, которые не дадут удалить запись ?
Причем нужно некое универсальное решение, которое можно прикрутить куда угодно, к каким угодно таблицам.

Суть проблемы:
Получаю исключение, при попытке удалить запись:

The DELETE statement conflicted with the REFERENCE constraint "FK__User__documentId__". 
The conflict occurred in database "MyBase", table "User.User", column 'documentId'.
The statement has been terminated.


И тогда появился вопрос, а можно ли перед удаление сначала выяснить получится ли запись удалить ?

А может.
А может проще таки попытаться удалить, словить исключение и распарсить его, чтобы пользователю просто вывести это сообщение в читаемом виде: "нельзя удалить пользователя, т.к. к нему привязаны документы" ?

Второй вариант мне кажется даже более интересным. Т.к. в первом решении мы получаем классическую проблему "race condition", когда между проверкой и действием может произойти нечто, что приведет к устареванию проверки.

Кто нибудь копал в этом направлении ?
8 фев 16, 17:24    [18789070]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Собственно. А можно в t-sql распарсить данное исключение ?

Можно ли получить эти параметры (table "User.User", column 'documentId') ?
Или все зашито в строку и нужно самостоятельно извлекать через регэксп ? в Ms sql вообще есть регэкспы ?

Как вообще решить данную задачу ?

Я хочу, чтобы процедура User_Delete в случае невозможности удаления по внешнему ключу - сама распарсила исключение и вернула его в Output параметре в более-менее читабельном виде: "Не удается удалить запись из таблицы: user.user, т.к. на нее есть ссылка из таблицы user.document".
Это возможно ?
8 фев 16, 17:31    [18789100]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
ProBiotek,

автор
А может.

Может.

автор
Это возможно ?

возможно.

автор
Или все зашито в строку и нужно самостоятельно извлекать через регэксп ? в Ms sql вообще есть регэкспы ?

есть
8 фев 16, 17:56    [18789208]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ther
Member

Откуда:
Сообщений: 839
1. Гуглите в сторону получения всех внешних ключей для данной таблицы/записи. Получаете список таблиц
2. Выбираете из таблиц данные
3. Если данные есть, значит есть ссылки на данную запись
8 фев 16, 17:56    [18789209]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
WarAnt,

А где там регэкспы ?
разве что функция PATINDEX похожа. Но это не то. Она возвращает позицию искомой строки. Но нам то нужно получить динамиески сконфигурированную строку.
8 фев 16, 18:07    [18789270]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8812
ProBiotek,

как бэ это ограничение не дает удалить лишнего без понимания - чего удаляешь-то. А вы хотите сломать защиту, спичечку вставить - и поедет ого-го как!
8 фев 16, 18:21    [18789335]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
ProBiotek
Причем нужно некое универсальное решение, которое можно прикрутить куда угодно, к каким угодно таблицам.

Кто нибудь копал в этом направлении ?


Можно предложить такое решение

BEGIN TRY 
	BEGIN TRAN 

		DELETE FROM T WHERE ID = 8888 

	ROLLBACK TRAN 

	PRINT N'Ссылок нет';
END TRY 
BEGIN CATCH 

	PRINT N'Ссылки есть';
	ROLLBACK 
	
END CATCH 


Вопрос в том, что вы пытаетесь достичь. Если единоразово почистить данные это одно.

Если надо встроить это в рабочий софт, то что-то не так с архитектурой.
8 фев 16, 18:33    [18789382]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
ProBiotek
WarAnt,

А где там регэкспы ?
разве что функция PATINDEX похожа. Но это не то. Она возвращает позицию искомой строки. Но нам то нужно получить динамиески сконфигурированную строку.


а что такое "динамиески сконфигурированную строку" в контексте регекспа?
8 фев 16, 18:36    [18789400]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Владислав Колосов
ProBiotek,

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


Не понял ничего. О чем вы ?
Я не хочу сломать защиту. Наоборот я хочу выводить юзеру читабельное сообщение. А не
"The DELETE statement conflicted with the REFERENCE constraint "FK__User__documentId__". 
The conflict occurred in database "MyBase", table "User.User", column 'documentId'."
8 фев 16, 18:45    [18789457]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
_djХомяГ
Guest
Ну обрамите в try catch как уже сказали выше , по номеру ошибки ERROR_NUMBER() или по паттерну в ERROR_MESSAGE() определяйте что это за ошибка и выводите свою мессагу
8 фев 16, 18:49    [18789496]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
a_voronin

Если надо встроить это в рабочий софт, то что-то не так с архитектурой.


А что не так с архитектурой, если хотим удалить юзера, а на него ссылаются внешние ключи ?
Я просто хочу сообщить пользователю человеческим языком, что он не может удалить юзера, пока к нему привязаны документы. Это вполне возможная ситуации в многопользовательской среде. Предположим один юзер привязал документ к юзеру, которого другой пользователь хочет вот-вот удалить.

Пока прихожу к выводу, что мне проще ловить это исключение на клиенте, где я без проблем смогу распарсить его регэкспом, получить константы "table "User.User", column 'documentId'." и отобразить юзеру это в читабельном виде.

Интересно просто как другие решают данную проблему. Или всем пофигу что у пользователя может свалится это исключение ? :)
8 фев 16, 18:51    [18789510]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
_djХомяГ
Ну обрамите в try catch как уже сказали выше , по номеру ошибки ERROR_NUMBER() или по паттерну в ERROR_MESSAGE() определяйте что это за ошибка и выводите свою мессагу


Ну к этому я и пришел.
Хотело бы только это на стороне сервера делать (выцеплять имена столбцов и таблиц из текста ошибки). Но пока не знаю как. Вроде как в MS SQL нету регэкспов. В принципе если выбрасывать это исключение на клиента, то теоретически задача решаема. Просто хотелось бы это все на сервере делать.
8 фев 16, 18:53    [18789523]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
ProBiotek,

Регэкспов из коробки нет. Можно прикрутить обертку на CLR.
Но в данном случае они не особо нужны.
Достаточно отловить ошибку по коду и вытащить из сообщения имя ограничения. Все остальное легко добывается из метаданных.
8 фев 16, 18:59    [18789560]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
WarAnt
ProBiotek
WarAnt,

А где там регэкспы ?
разве что функция PATINDEX похожа. Но это не то. Она возвращает позицию искомой строки. Но нам то нужно получить динамиески сконфигурированную строку.


а что такое "динамиески сконфигурированную строку" в контексте регекспа?


эм. ну да, я наверное не верно выразился.

суть заключается в том, что мне нужно вытащить из такой строки:

"... Ошибка='динамически генерируемый текст 123'" текст 'динамически генерируемый текст 123'
В примере с текстом исключения это 'table User.User'и 'column documentId'.

На клиентской стороне я примерно представляю как это сделать через регэкспы. А как в MS SQL ?
8 фев 16, 18:59    [18789561]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
invm
ProBiotek,

Регэкспов из коробки нет. Можно прикрутить обертку на CLR.
Но в данном случае они не особо нужны.
Достаточно отловить ошибку по коду и вытащить из сообщения имя ограничения. Все остальное легко добывается из метаданных.


вытаскивать операцией PATINDEX находя текст идущий после слова "constraint " ?

ну вот еще нужно понять как через метаданные тогда извлечь то, что нужно. Поищу.
8 фев 16, 19:03    [18789593]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
ProBiotek
WarAnt
пропущено...


а что такое "динамиески сконфигурированную строку" в контексте регекспа?


эм. ну да, я наверное не верно выразился.

суть заключается в том, что мне нужно вытащить из такой строки:

"... Ошибка='динамически генерируемый текст 123'" текст 'динамически генерируемый текст 123'
В примере с текстом исключения это 'table User.User'и 'column documentId'.

На клиентской стороне я примерно представляю как это сделать через регэкспы. А как в MS SQL ?


declare @str varchar(100) = 'Ошибка=''динамически генерируемый текст 123'''
select substring(@str, charindex('Ошибка=', @str) + 7, 100)
8 фев 16, 19:05    [18789605]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
ProBiotek,

я когда-то таким интересовался и вот такое посоветовали
хотя я так до реализации и не добрался
а вообще удалять и смотреть что получится на мой взгляд как-то некрасиво
считаю что правильнее пробежаться по связям и сообщить
тяжело предположить что их там какое-то неограниченное число
8 фев 16, 19:10    [18789639]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
ProBiotek
a_voronin
Если надо встроить это в рабочий софт, то что-то не так с архитектурой.


А что не так с архитектурой, если хотим удалить юзера, а на него ссылаются внешние ключи ?
Я просто хочу сообщить пользователю человеческим языком, что он не может удалить юзера, пока к нему привязаны документы. Это вполне возможная ситуации в многопользовательской среде. Предположим один юзер привязал документ к юзеру, которого другой пользователь хочет вот-вот удалить.

Пока прихожу к выводу, что мне проще ловить это исключение на клиенте, где я без проблем смогу распарсить его регэкспом, получить константы "table "User.User", column 'documentId'." и отобразить юзеру это в читабельном виде.

Интересно просто как другие решают данную проблему. Или всем пофигу что у пользователя может свалится это исключение ? :)


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

то есть правильный путь, это сначала select, а потом delete, а не сначала delete, а потом raisserror.
Raisserror, это уже нарушение работы системы, это тоже самое, что например "Java heap space out of memory" в java
8 фев 16, 19:13    [18789660]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
WarAnt,

хехе.
А теперь попробуйте то же самое, но при условии, что вы не знаете что там после "Ошибка" (вы то ее в substring первым параметром передаете). Вот как раз это и нужно выцепить. Ту самую, динамическую часть.

Т.е. вот. Имеем такой шаблон:
'table User.User'и 'column documentId'
Константы тут только table и column. А нужно извлечь то, что за ними идет.

В приниципе invm предложил вариант. Только уж не знаю что проще: найти имя ограничения и потом шарится по метаданным. Или, все же, напрямую выдернуть эти имена таблиц и столбцов.
8 фев 16, 19:13    [18789661]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
ProBiotek
a_voronin
Если надо встроить это в рабочий софт, то что-то не так с архитектурой.


А что не так с архитектурой, если хотим удалить юзера, а на него ссылаются внешние ключи ?
Я просто хочу сообщить пользователю человеческим языком, что он не может удалить юзера, пока к нему привязаны документы. Это вполне возможная ситуации в многопользовательской среде. Предположим один юзер привязал документ к юзеру, которого другой пользователь хочет вот-вот удалить.

Пока прихожу к выводу, что мне проще ловить это исключение на клиенте, где я без проблем смогу распарсить его регэкспом, получить константы "table "User.User", column 'documentId'." и отобразить юзеру это в читабельном виде.

Интересно просто как другие решают данную проблему. Или всем пофигу что у пользователя может свалится это исключение ? :)


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

Либо вам надо переключить эти документы на его начальника или сформулировать иную бизнес логику. Удаление пользователя -- это бизнес-процесс, который должен быть правильно запрограммирован. В противной случае просто напишите каскадное удаление и все.
8 фев 16, 19:16    [18789682]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
ProBiotek
ну вот еще нужно понять как через метаданные тогда извлечь то, что нужно. Поищу.
sys.foreign_keys, sys.foreign_keys_columns
Либо через представления в INFORMATION_SCHEMA.
8 фев 16, 19:16    [18789684]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
ProBiotek
Хотело бы только это на стороне сервера делать (выцеплять имена столбцов и таблиц из текста ошибки).
На стороне сервера и так формируется нормальное читаемое сообщение.
Просто оно вам не нравится, и вы хотите сформировать своё, и на другом языке.
Так формируйте, раз хотите.

Встроенных средств для получения имён столбцов на сервере разумеется нет, как и везде.
Честно говоря, не слышал, что бы можно было, например, в C# получить текст ошибки не в виде текста, а в виде структурированных данных, с именами классов, методов, переменных в виде специальных структур.

ProBiotek
Интересно просто как другие решают данную проблему. Или всем пофигу что у пользователя может свалится это исключение ? :)
А как вы решаете эту проблему (в случаях, не касающихся MS SQL)?
У вас же может произойти ошибка в программе на ASP.NET или WinForms (взял из вашего профиля)?
У вас есть какой то универсальный обработчик исключений, который по тексту исключения формирует понятное для пользователя сообщение?

Общепринятый метод такой:
1. Для массовых случаев пишется вывод соответствующих сообщений об ошибках. Т,е. прямо на обработчике нажатия кнопки в интерфейсе. IF не удалось удалить заказ, потому что он уже отгружен заказчику, THEN выводим сообщение "нельзя удалить, потому что заказ отгружен заказчику"
2. Для редких случаев пользователю пишется "ошибка", а по кнопке "показать детали" он может посмотреть оригинальные тексты эксепшенов, которые может передать в службу поддержки.

И не надо забывать, что констрейны делаются для страховки от ошибок программиста. Это один из уровней проверки одного и того же.
Т.е. пользователь и не должен иметь возможность удалить документ, если к нему привязаны другие документы - это всё регулируется программой, программистом, кнопка должна быть недоступна для нажатия. Но если программист лоханулся, то вот для этого случая есть констрейн.
8 фев 16, 19:17    [18789690]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
WarAnt
a_voronin вам пытается донести, что в нормальных архитектурах принято, сначала искать зависимости, а потом удалять или не удалять данные по результату поиска, а не тупо удалять данные в надежде, что не выскочит ошибка целостноности.
Констнрейнты придуманы не для того чтобы по ним определять возможность, а для того чтобы сохранять целостность структуры данных.

то есть правильный путь, это сначала select, а потом delete, а не сначала delete, а потом raisserror.
Raisserror, это уже нарушение работы системы, это тоже самое, что например "Java heap space out of memory" в java


Мне кажется или тут у нас налицо проблемы "Неповторяющееся чтение" и "Фантомное чтение", race condition (состояние гонки) ?

Представьте что будет если один пользователь цепляет документы юзеру, а другой пользователь юзера удаляет.
8 фев 16, 19:18    [18789697]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
ProBiotek

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


Потому что надо делать вот так:

BEGIN TRAN 

-- блокировка записи пользователя 
-- проверка связей
-- принятие решение об удалении 
-- удаленние 

COMMIT TRAN 


BEGIN TRAN 

-- блокировка записи пользователя 
-- добавление связи 

COMMIT TRAN 
8 фев 16, 19:21    [18789709]     Ответить | Цитировать Сообщить модератору
 Re: Перед удалением - узнать есть ли на запись внешние ключи. Как ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
alexeyvg
Т.е. пользователь и не должен иметь возможность удалить документ, если к нему привязаны другие документы - это всё регулируется программой, программистом, кнопка должна быть недоступна для нажатия. Но если программист лоханулся, то вот для этого случая есть констрейн.


Тут может быть и более тонкий случай. Приложение прочитало записи, что-то с ними сделала, проверило и т.п., а в это время другая сессия изменила эти записи. В ORM слоях в принципе сейчас сделаны механизмы отслеживания таких вещей, но они срабатывают лишь по команде сохранить.
8 фев 16, 19:25    [18789731]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить