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

Откуда: Украина, Хмельницкий
Сообщений: 741
Привет.

Есть необходимость удаления выбранной записи из таблицы. На эту таблицу есть внешние ключи из других таблиц.
Вопрос: как удалить эту запись со всеми зависимыми, учитывая, что удаляемые записи в зависимых таблицах "связаны" внешними ключами с другими таблицами (рекурсия однако)?
10 ноя 11, 15:53    [11576090]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
trew
Member

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

Каскадное удаление записей, создание внешнего ключа.
10 ноя 11, 15:57    [11576140]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
trew
Member

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

http://forum.shelek.ru/index.php/topic,12824.0.html
10 ноя 11, 15:59    [11576164]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
Jetus
Member

Откуда: Украина, Хмельницкий
Сообщений: 741
trew
Jetus,
Каскадное удаление записей, создание внешнего ключа.

Понимаю, но база уже существует и живет.
Таблиц много, а внешних ключей еще больше.
Большинство ключей не имеют каскадного удаления для сохранения целостности данных.
10 ноя 11, 15:59    [11576174]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
baracs
Member

Откуда: Москва
Сообщений: 7217
Jetus
Таблиц много, а внешних ключей еще больше.
Для 2000-го - системная таблица sysforeignkeys;

Для 2005 и старше - представление sys.foreign_keys.
10 ноя 11, 16:30    [11576509]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
Jetus
Member

Откуда: Украина, Хмельницкий
Сообщений: 741
baracs
Jetus
Таблиц много, а внешних ключей еще больше.
Для 2000-го - системная таблица sysforeignkeys;
Для 2005 и старше - представление sys.foreign_keys.

Как вытащить список внешних ключей - я знаю. Вопрос в том, что делать дальше.

У меня есть идея, только чур ногами сразу не бить:
1. Открываем транзакцию
2. Получаем список внешних ключей, у которых DELETE_RULE <> 'CASCADE'
3. Дропаем их и создаем такие же, но с DELETE_RULE = 'CASCADE'
4. Удаляем нужную запись
5. Дропаем соданные нами внешних ключи их и создаем такие же, первоначальные
6. Коммитим транзакцию.

Будет ли такое работать? Может лучше по другому как-то?
10 ноя 11, 16:36    [11576563]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
Jetus
Member

Откуда: Украина, Хмельницкий
Сообщений: 741
Jetus
Будет ли такое работать? Может лучше по другому как-то?

"Introducing FOREIGN KEY constraint 'FK_astManufacturer_Company_CompanyID' on table 'astManufacturer' may cause cycles or multiple cascade paths."
Уже вижу, что работать такое не будет из-за multiple cascade paths

Хотелось бы услышать какие-то варианты.
10 ноя 11, 17:08    [11576919]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
С помощью sys.foreign_keys и рекурсивного CTE генерируете скрипт, состоящий из инструкций delete в нужной последовательности. Полученный скрипт запихиваете в триггер instead of delete на целевой таблице. Можно попробовать все это автоматизировать, написав соответствующий DDL-триггер.

ЗЫ: Каскадные удаления зло, от которого надо избавляться.
10 ноя 11, 17:13    [11576971]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
Jetus
Member

Откуда: Украина, Хмельницкий
Сообщений: 741
invm
С помощью sys.foreign_keys и рекурсивного CTE генерируете скрипт, состоящий из инструкций delete в нужной последовательности...

Я так и чувствовал, что придется рекурсивно гонять удаление... вопрос теперь в том, как удалить именно нужные записи.
10 ноя 11, 18:01    [11577498]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Jetus
Я так и чувствовал, что придется рекурсивно гонять удаление... вопрос теперь в том, как удалить именно нужные записи.
Не рекурсивно гонять удаление, а с помощью рекурсивного CTE получить скрипт для удаления.
10 ноя 11, 18:18    [11577645]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
Jetus
Member

Откуда: Украина, Хмельницкий
Сообщений: 741
invm
С помощью sys.foreign_keys и рекурсивного CTE...

У меня есть скрипт, который вытягивает все внешние ключи на указанную таблицу:
SELECT PK.TABLE_NAME AS TableName, PT.COLUMN_NAME AS FieldName,
    FK.TABLE_NAME AS ForeignTableName, CU.COLUMN_NAME AS ForeignFieldName,
    C.CONSTRAINT_NAME AS FKName
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
    INNER JOIN (
        SELECT i1.TABLE_NAME, i2.COLUMN_NAME
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
    ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME = 'Company'

Пожалуйста, подскажите, как мне при помощи него и CTE вытянуть все остальные вложенные таблицы?
10 ноя 11, 18:21    [11577674]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
mike909
Member

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

Для информации ForeignKey - удалить запись, только если на нее нет ссылок
подправьте под свою задачу ...
10 ноя 11, 19:19    [11577930]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
Jetus
Member

Откуда: Украина, Хмельницкий
Сообщений: 741
mike909
подправьте под свою задачу ...

Код красивый, только я не наблюдаю в нем рекурсии.
Спасибо, но он мало чем может мне помочь.
11 ноя 11, 11:15    [11580032]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Служебная функция:
if object_id('dbo.fnGetJoinConditionByForeignKeyConstraint', 'FN') is not null
 drop function dbo.fnGetJoinConditionByForeignKeyConstraint;
go
create function dbo.fnGetJoinConditionByForeignKeyConstraint
(
	@constraint_id int,
	@parent_alias sysname,
	@referenced_alias sysname	
)
returns nvarchar(max)
as
begin
 declare @result nvarchar(max);
 
 select
  @result = (
   select
    N' and ' + isnull(nullif(ltrim(rtrim(@referenced_alias)), N'') + N'.', N'') + quotename(rc.name) +
    N' = ' +
    isnull(nullif(ltrim(rtrim(@parent_alias)), N'') + N'.', N'') + quotename(pc.name)
   from
    sys.foreign_key_columns fkc join
    sys.columns pc on pc.object_id = fkc.parent_object_id and pc.column_id = fkc.parent_column_id join
    sys.columns rc on rc.object_id = fkc.referenced_object_id and rc.column_id = fkc.referenced_column_id
   where
    fkc.constraint_object_id = @constraint_id
   for xml path(''), type
  ).value('.', 'nvarchar(max)')
 
 return stuff(@result, 1, 5, '');
end;
go
CTE:
declare @o int = object_id('RootTable', 'U');

with x as
(
 select
  fk.parent_object_id,
  fk.name,
  1 as fk_Level,
  cast(N't1' as sysname) as fk_TableAlias,
  quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) as fk_TableName,
  quotename(object_schema_name(fk.referenced_object_id)) + N'.' + quotename(object_name(fk.referenced_object_id)) + N' t0 join ' +
   quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) + N' t1 on ' +
   dbo.fnGetJoinConditionByForeignKeyConstraint(fk.object_id, N't0', N't1') as fk_FromClause,
  1 as fk_PathIsValid,
  cast(quotename(fk.name, '()') as nvarchar(max)) as fk_Path
 from
  sys.foreign_keys fk
 where
  fk.referenced_object_id = @o

 union all
 
 select
  fk.parent_object_id,
  fk.name,
  x.fk_Level + 1,
  cast(N't' + cast(x.fk_Level + 1 as nvarchar(10)) as sysname) as fk_TableAlias,
  quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) as fk_TableName,
  x.fk_FromClause + N' join ' +
   quotename(object_schema_name(fk.referenced_object_id)) + N'.' + quotename(object_name(fk.referenced_object_id)) + N' ' + x.fk_TableAlias + N' join ' +
   quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) + N' ' + N't' + cast(x.fk_Level + 1 as nvarchar(10)) + N' on ' +
   dbo.fnGetJoinConditionByForeignKeyConstraint(fk.object_id, x.fk_TableAlias, N't' + cast(x.fk_Level + 1 as nvarchar(10))),
  case when x.fk_Path like '%' + quotename(fk.name, '()') + '%' then 0 else 1 end,
  x.fk_Path + quotename(fk.name, '()')
 from
  x join
  sys.foreign_keys fk on fk.referenced_object_id = x.parent_object_id
 where
  x.fk_PathIsValid = 1
)
select
 N'delete ' + x.fk_TableAlias + N' from ' + x.fk_FromClause
from
 x
order by
 x.fk_Level desc
option
 (maxrecursion 0);
11 ноя 11, 13:23    [11581230]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
В предыдущем посте ошибка, CTE должно выглядеть так:
declare @o int = object_id('dbo.WPlaces', 'U');

with x as
(
 select
  fk.parent_object_id,
  fk.name,
  1 as fk_Level,
  cast(N't1' as sysname) as fk_TableAlias,
  quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) as fk_TableName,
  quotename(object_schema_name(fk.referenced_object_id)) + N'.' + quotename(object_name(fk.referenced_object_id)) + N' t0 join ' +
   quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) + N' t1 on ' +
   dbo.fnGetJoinConditionByForeignKeyConstraint(fk.object_id, N't0', N't1') as fk_FromClause,
  1 as fk_PathIsValid,
  cast(quotename(fk.name, '()') as nvarchar(max)) as fk_Path
 from
  sys.foreign_keys fk
 where
  fk.referenced_object_id = @o

 union all
 
 select
  fk.parent_object_id,
  fk.name,
  x.fk_Level + 1,
  cast(N't' + cast(x.fk_Level + 1 as nvarchar(10)) as sysname) as fk_TableAlias,
  quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) as fk_TableName,
  x.fk_FromClause + N' join ' +
   quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) + N' ' + N't' + cast(x.fk_Level + 1 as nvarchar(10)) + N' on ' +
   dbo.fnGetJoinConditionByForeignKeyConstraint(fk.object_id, x.fk_TableAlias, N't' + cast(x.fk_Level + 1 as nvarchar(10))),
  case when x.fk_Path like '%' + quotename(fk.name, '()') + '%' then 0 else 1 end,
  x.fk_Path + quotename(fk.name, '()')
 from
  x join
  sys.foreign_keys fk on fk.referenced_object_id = x.parent_object_id
 where
  x.fk_PathIsValid = 1
)
select
 N'delete ' + x.fk_TableAlias + N' from ' + x.fk_FromClause
from
 x
order by
 x.fk_Level desc
option
 (maxrecursion 0);
11 ноя 11, 13:37    [11581404]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
В предыдущем посте ошибка, CTE должно выглядеть так:
declare @o int = object_id('dbo.WPlaces', 'U');

with x as
(
 select
  fk.parent_object_id,
  fk.name,
  1 as fk_Level,
  cast(N't1' as sysname) as fk_TableAlias,
  quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) as fk_TableName,
  quotename(object_schema_name(fk.referenced_object_id)) + N'.' + quotename(object_name(fk.referenced_object_id)) + N' t0 join ' +
   quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) + N' t1 on ' +
   dbo.fnGetJoinConditionByForeignKeyConstraint(fk.object_id, N't0', N't1') as fk_FromClause,
  1 as fk_PathIsValid,
  cast(quotename(fk.name, '()') as nvarchar(max)) as fk_Path
 from
  sys.foreign_keys fk
 where
  fk.referenced_object_id = @o

 union all
 
 select
  fk.parent_object_id,
  fk.name,
  x.fk_Level + 1,
  cast(N't' + cast(x.fk_Level + 1 as nvarchar(10)) as sysname) as fk_TableAlias,
  quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) as fk_TableName,
  x.fk_FromClause + N' join ' +
   quotename(object_schema_name(fk.parent_object_id)) + N'.' + quotename(object_name(fk.parent_object_id)) + N' ' + N't' + cast(x.fk_Level + 1 as nvarchar(10)) + N' on ' +
   dbo.fnGetJoinConditionByForeignKeyConstraint(fk.object_id, x.fk_TableAlias, N't' + cast(x.fk_Level + 1 as nvarchar(10))),
  case when x.fk_Path like '%' + quotename(fk.name, '()') + '%' then 0 else 1 end,
  x.fk_Path + quotename(fk.name, '()')
 from
  x join
  sys.foreign_keys fk on fk.referenced_object_id = x.parent_object_id
 where
  x.fk_PathIsValid = 1
)
select
 N'delete ' + x.fk_TableAlias + N' from ' + x.fk_FromClause
from
 x
order by
 x.fk_Level desc
option
 (maxrecursion 0);
11 ноя 11, 13:37    [11581406]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
Jetus
Member

Откуда: Украина, Хмельницкий
Сообщений: 741
invm,

большое спасибо.

Все delete-запросы получаются с указанной таблицей во главе, то есть удаление происходит только из исходной таблицы.
Но общую суть я понял, спасибо.
11 ноя 11, 16:56    [11583498]     Ответить | Цитировать Сообщить модератору
 Re: Удалить запись из таблицы со всеми зависимыми  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Jetus
Все delete-запросы получаются с указанной таблицей во главе, то есть удаление происходит только из исходной таблицы.
Неверный вывод.
11 ноя 11, 17:08    [11583632]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить