Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Каскадное удаление  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 327
Всем привет.

Появилась необходимость удалить несколько строк из топовой таблицы - справочника.
Так же требуется удалить все записи из всех таблиц , связанных с этим справочником.
Референсы к этой таблице охватывают почти все таблицы БД.
Таблиц в БД очень много.
Где - то FK настроены в режиме NO ACTION , где то ON DELETE CASCADE - так что простым delete запись из справочника не удалить.

В начале предположил, что если пересоздать все FK ,завязанные по этим референсам , на ON DELETE CASCADE, то возможно и получиться выполнить один единственный delete.
Сформировал список связанных таблиц и атрибутов для пересоздания FK :
WITH t ([object_id],C_Schema_Name,C_Table_Name,N_Lvl,C_Up_Table_Name,C_Column_Name,C_Column_Name_Ref,C_FK_Name,N_Action_Mode)
AS
(
	SELECT  ao.[object_id]		[object_id],
			s.name				C_Schema_Name,
			ao.name				C_Table_Name,
			CONVERT(INT,0)		N_Lvl,
			CONVERT(NVARCHAR(200),NULL )	C_Up_Table_Name,
			CONVERT(NVARCHAR(200),NULL )	C_Column_Name,
			CONVERT(NVARCHAR(200),NULL )	C_Column_Name_Ref,
			CONVERT(NVARCHAR(200),NULL )	C_FK_Name,
			CONVERT(TINYINT,NULL )			N_Action_Mode		
	FROM sys.all_objects AS ao
		INNER JOIN sys.schemas AS s
			ON ao.[schema_id] = s.[schema_id]		
	WHERE ao.[object_id] = :N_Object_Id
	UNION ALL
	SELECT	
			ao.[object_id]		[object_id],
			ao.C_Schema_Name,
			ao.C_Table_Name,
			ts.N_Lvl + 1			N_Lvl,
			CONVERT(NVARCHAR(200),ts.C_Schema_Name +'.' + ts.C_Table_Name),
			ao.C_Column_Name,
			ao.C_Column_Name_Ref,
			ao.name,
			ao.delete_referential_action 
	FROM t ts
		CROSS APPLY
		(
			SELECT   
				   ao3.[object_id],
				   s.name			C_Schema_Name,
				   ao3.name			C_Table_Name,
				   convert(NVARCHAR(200),ac.name)			C_Column_Name,
				   convert(NVARCHAR(200),ac1.name)			C_Column_Name_Ref,
				   convert(NVARCHAR(200),fk.name)			name,
				   FK.delete_referential_action,
				   ROW_NUMBER() OVER (PARTITION BY ao3.[object_id] ORDER BY ao3.[object_id]) N
			FROM sys.foreign_keys AS fk 
				INNER JOIN sys.all_objects AS ao3
					ON ao3.[object_id] = fk.parent_object_id
						AND ao3.[type] = 'U'
				INNER JOIN sys.schemas AS s
					ON ao3.[schema_id] = s.[schema_id]	
				INNER JOIN sys.foreign_key_columns AS fkc
					ON fk.[object_id] = fkc.constraint_object_id
				INNER JOIN sys.all_columns AS ac 
					ON ac.[object_id] = fkc.parent_object_id AND ac.column_id = fkc.parent_column_id
				INNER JOIN sys.all_columns AS ac1 
					ON ac1.[object_id] = fkc.referenced_object_id AND ac1.column_id = fkc.referenced_column_id							
			WHERE fk.referenced_object_id = ts.[object_id]
				AND FK.parent_object_id <> fk.referenced_object_id
		) ao	
	WHERE  AO.N = 1
	
)
SELECT  C_Schema_Name, C_Table_Name,N_Lvl,C_Up_Table_Name ,C_Column_Name,C_Column_Name_Ref, C_FK_Name, N_Action_Mode
FROM t 
GROUP BY C_Schema_Name, C_Table_Name,N_Lvl,C_Up_Table_Name ,C_Column_Name,C_Column_Name_Ref, C_FK_Name, N_Action_Mode
ORDER BY MAX(T.N_Lvl) DESC ,MIN(T.N_Lvl)  DESC 


Но в результате пересоздания FK получаю ошибку Msg 1785, Level 16, State 0, Line 7 -
*может привести к появлению циклов или множественных каскадных путей. Укажите ON DELETE NO ACTION или ON UPDATE NO ACTION* 
- что ,вроде бы, логично.
3 авг 17, 16:09    [20698476]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Col
Member

Откуда: Торонто
Сообщений: 180
Cristiano_Rivaldo,
https://www.sql.ru/forum/970297/skript-na-vkluchenie-kaskadnogo-udaleniya-po-vsey-baze-dannyh
3 авг 17, 16:41    [20698585]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 327
Т.е предлагаете пересоздать абсолютно все FK, а не только референсные ?
3 авг 17, 17:03    [20698668]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Владислав Колосов
Member

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

удачи Вам с каскадным удалением!
3 авг 17, 17:11    [20698687]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Col
Member

Откуда: Торонто
Сообщений: 180
Cristiano_Rivaldo,

Я там пример давал удаления без необходимости рубить констрейны, он не оптимален но работает
'edit_this' это колонка идентифицатора, ну как-то же у Вас данные идентифицириваны.
Он по всей базе удаляет, запускать до тач пор пока все не удалит.
3 авг 17, 20:40    [20699087]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 327
Col
Cristiano_Rivaldo,

Я там пример давал удаления без необходимости рубить констрейны
4 авг 17, 09:43    [20699668]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Cristiano_Rivaldo
Member

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

Мне кажется ваш скрипт абсолютно не работает. Там нужно на каждом уровне референса переопределять 'edit_this' - он не везде один и тот же. И удаление начинать с таблиц на самом последнем уровне вложенности относительно референса.
4 авг 17, 09:47    [20699686]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Col
Member

Откуда: Торонто
Сообщений: 180
Cristiano_Rivaldo
Col,
он не везде один и тот же

Это уже не каскадное удаление а рекурсивное.
Вот тут:
https://www.sql.ru/forum/894319/udalit-zapis-iz-tablicy-so-vsemi-zavisimymi
4 авг 17, 18:26    [20701851]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Автор Поста
Guest
Col,

Я все что писал - это решения рекурсии. Если это было все так просто - я бы тут не писал. Жаль что ты понял это только через 2 поста. Без обид. А так по существу проблема в ms sql не решается без перепроектировки таблиц. Это не моя логика -я в ней пробегом. Я хочу ее решить - но вроде бы это не под силу.Можно искать выход через рекурсию через выбор PK - по линкам и через джойна на последний уровень референса. Возможно кто-то и делал....
5 авг 17, 21:11    [20702966]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
Автор Поста
А так по существу проблема в ms sql не решается без перепроектировки таблиц.
Если вы не в курсе, в MSSQL данные можно удалять не только через cascade delete.
5 авг 17, 21:22    [20702992]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Автор поста_1
Guest
Гавриленко Сергей Алексеевич,

Просвети же меня , дурака. Только смотри первый пост
5 авг 17, 21:26    [20702996]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
Автор поста_1
Гавриленко Сергей Алексеевич,

Просвети же меня , дурака. Только смотри первый пост
Как-то так:
delete ... from [Самая дальняя от топовой таблица] where <условия>
delete ... from [Предыдущая от самой дальней от топовой таблица] where <условия>
...
delete ... from [Топовая таблица] where <условия>
5 авг 17, 21:31    [20703003]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Автор_ЭтОЙ_фИГНИ
Guest
Гавриленко Сергей Алексеевич,

Ваша помощь не оценима. Спасибо. Я и не знал что все так просто...
5 авг 17, 21:33    [20703007]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
Автор_ЭтОЙ_фИГНИ
Гавриленко Сергей Алексеевич,

Ваша помощь не оценима. Спасибо. Я и не знал что все так просто...
Ну, когда убедитесь, что волшебной кнопки нет, наймите программиста что ли.
5 авг 17, 21:36    [20703011]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Последний_Пост_Автора
Guest
Гавриленко Сергей Алексеевич,
А с тобой кто общается ? Ты решение так и не дал. Точнее, то что ты дал - это не решение. Я так же мог бы писать под каждым постом, троля всех. Только я этим не занимаюсь.
5 авг 17, 21:42    [20703020]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
Последний_Пост_Автора
Я так же мог бы писать под каждым постом, троля всех. Только я этим не занимаюсь.
Пфф. Именно троллингом вы и занимаетесь. Типа, "у меня есть база непонятной структуры, мне разбираться лень, но как одним delete сделать хорошо".
5 авг 17, 21:47    [20703033]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Все_Же_Я_Наврал
Guest
Гавриленко Сергей Алексеевич,
Ты переспорил. Я сдаюсь.
5 авг 17, 21:57    [20703040]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
Автор Поста
А так по существу проблема в ms sql не решается без перепроектировки таблиц
Решается. Только придется попотеть.

На каждую таблицу пишется триггер instead of delete. В обычном режиме эти триггеры запрещены. Когда нужно удалить что-либо вместе с детьми, внуками и т.д., триггеры разрешаются. Желательно в одной транзакции с удалением. После того как темные делишки будут обтяпаны, триггеры опять запрещаются. В той же транзакции.

Каждый триггер определяет по метаданным дочерние таблицы и удаляет из них соответствующие строки. Затем удаляет строки из своей таблицы.

Итого, с помощью такой вот неявной рекурсии ваше желание осуществится. При условии, что уровень вложенности вызовов триггеров не превысит 32.
5 авг 17, 22:36    [20703065]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Каскад при этих условиях
автор
Референсы к этой таблице охватывают почти все таблицы БД.


скорее всего получить пачку дэдлоков и остановит работу всех пользователей. Если там только один автор сидит, то без разницы.
6 авг 17, 10:43    [20703341]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Drop Table
Guest
Прошу прощения за взгляд с другой колокольни, но я бы решал эту задачу на прикладном языке.
Это обычный концевой (post-ordered) обход дерева.
Будет работать не очень быстро, но задача разовая, как я понимаю.
Делов написания скрипта на один день.
7 авг 17, 18:47    [20706431]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Озо
Guest
Я решала такую задачу рекурсивным запросом к объектам information_schema для получения скрипта на удаление от таблиц где на удаляемое никто не ссылается и до основной )))))
7 авг 17, 19:21    [20706500]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
не удаляем, а помечаем запись "не активно"
8 авг 17, 11:59    [20707836]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
StarikNavy
не удаляем, а помечаем запись "не активно"

Первый дельный совет от опытного человека
8 авг 17, 12:09    [20707884]     Ответить | Цитировать Сообщить модератору
 Re: Каскадное удаление  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Мне кажется, что это какое-то извращение. Пытаться удалить то, что тщательно сделано неудаляемым.
8 авг 17, 18:51    [20709280]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить