Я запускаю пакет SSIS, который заменит данные для нескольких таблиц из FlatFiles на существующие таблицы в базе данных.
Мой пакет обрезает таблицы, а затем вставляет новые данные. Когда я запускаю свой пакет SSIS, я получаю исключение из-за внешних ключей.
Можно ли отключить ограничения, запустить мой импорт, а затем снова включить их?
Чтобы отключить ограничения внешнего ключа:
DECLARE @sql NVARCHAR(MAX) = N'';
;WITH x AS
(
SELECT DISTINCT obj =
QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
+ QUOTENAME(OBJECT_NAME(parent_object_id))
FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;
' FROM x;
EXEC sp_executesql @sql;
Чтобы снова включить:
DECLARE @sql NVARCHAR(MAX) = N'';
;WITH x AS
(
SELECT DISTINCT obj =
QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
+ QUOTENAME(OBJECT_NAME(parent_object_id))
FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;
EXEC sp_executesql @sql;
Однако вы не сможете обрезать таблицы, вам нужно будет удалить их в правильном порядке. Если вам нужно усечь их, вам нужно полностью отказаться от ограничений и повторно создать их. Это просто сделать, если ограничения внешнего ключа - это простые, одностоловые ограничения, но определенно более сложные, если задействовано несколько столбцов.
Вот что вы можете попробовать. Чтобы сделать это частью вашего пакета SSIS, вам понадобится место для хранения определений FK во время работы пакета SSIS (вы не сможете сделать это всего за один script). Поэтому в некоторой базе данных утилиты создайте таблицу:
CREATE TABLE dbo.PostCommand(cmd NVARCHAR(MAX));
Затем в вашей базе данных вы можете иметь хранимую процедуру, которая делает это:
DELETE other_database.dbo.PostCommand;
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
+ ' ADD CONSTRAINT ' + fk.name + ' FOREIGN KEY ('
+ STUFF((SELECT ',' + c.name
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')
+ ') REFERENCES ' +
QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id))
+ '(' +
STUFF((SELECT ',' + c.name
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') + ');
' FROM sys.foreign_keys AS fk
WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0;
INSERT other_database.dbo.PostCommand(cmd) SELECT @sql;
IF @@ROWCOUNT = 1
BEGIN
SET @sql = N'';
SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
+ ' DROP CONSTRAINT ' + fk.name + ';
' FROM sys.foreign_keys AS fk;
EXEC sp_executesql @sql;
END
Теперь, когда ваш пакет SSIS завершен, он должен вызвать другую хранимую процедуру, которая:
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = cmd FROM other_database.dbo.PostCommand;
EXEC sp_executesql @sql;
Если вы делаете все это только ради того, чтобы усечь вместо удаления, я предлагаю просто взять удар и запустить удаление. Может быть, использовать модель восстановления с запасом, чтобы минимизировать влияние журнала. В общем, я не вижу, как это решение будет намного быстрее, чем просто использовать удаление в правильном порядке.
В 2014 году я опубликовал более подробную статью об этом здесь:
Используйте встроенную хранимую процедуру sp_msforeachtable.
Чтобы отключить все ограничения:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";
Чтобы включить все ограничения:
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";
Чтобы удалить все таблицы:
EXEC sp_msforeachtable "DROP TABLE ?";
Хорошая ссылка дается по адресу: http://msdn.microsoft.com/en-us/magazine/cc163442.aspx в разделе "Отключение всех внешних ключей"
Вдохновленный от него, можно сделать подход, создав временную таблицу и вставив ограничения в эту таблицу, а затем отменив ограничения и затем повторно применив их из этой временной таблицы. Хватит сказать, вот что я говорю о
SET NOCOUNT ON
DECLARE @temptable TABLE(
Id INT PRIMARY KEY IDENTITY(1, 1),
FKConstraintName VARCHAR(255),
FKConstraintTableSchema VARCHAR(255),
FKConstraintTableName VARCHAR(255),
FKConstraintColumnName VARCHAR(255),
PKConstraintName VARCHAR(255),
PKConstraintTableSchema VARCHAR(255),
PKConstraintTableName VARCHAR(255),
PKConstraintColumnName VARCHAR(255)
)
INSERT INTO @temptable(FKConstraintName, FKConstraintTableSchema, FKConstraintTableName, FKConstraintColumnName)
SELECT
KeyColumnUsage.CONSTRAINT_NAME,
KeyColumnUsage.TABLE_SCHEMA,
KeyColumnUsage.TABLE_NAME,
KeyColumnUsage.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
ON KeyColumnUsage.CONSTRAINT_NAME = TableConstraints.CONSTRAINT_NAME
WHERE
TableConstraints.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @temptable SET
PKConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@temptable tt
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ReferentialConstraint
ON tt.FKConstraintName = ReferentialConstraint.CONSTRAINT_NAME
UPDATE @temptable SET
PKConstraintTableSchema = TABLE_SCHEMA,
PKConstraintTableName = TABLE_NAME
FROM @temptable tt
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
ON tt.PKConstraintName = TableConstraints.CONSTRAINT_NAME
UPDATE @temptable SET
PKConstraintColumnName = COLUMN_NAME
FROM @temptable tt
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
ON tt.PKConstraintName = KeyColumnUsage.CONSTRAINT_NAME
--Now to drop constraint:
SELECT
'
ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']
DROP CONSTRAINT ' + FKConstraintName + '
GO'
FROM
@temptable
--Finally to add constraint:
SELECT
'
ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']
ADD CONSTRAINT ' + FKConstraintName + ' FOREIGN KEY(' + FKConstraintColumnName + ') REFERENCES [' + PKConstraintTableSchema + '].[' + PKConstraintTableName + '](' + PKConstraintColumnName + ')
GO'
FROM
@temptable
GO
Отключить все ограничения таблицы
ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName
- Включить все ограничения таблицы
ALTER TABLE TableName CHECK CONSTRAINT ConstraintName
не нужно запускать запросы для разворачивания FK на sql. Если у вас есть FK из таблицы A в B, вы должны:
Вы также можете указать адресату не проверять ограничения
Усечение таблицы не будет возможным, даже если вы отключите внешние ключи. Вы можете использовать delete, чтобы удалить все записи из таблицы, но имейте в виду, если вы используете delete команда для таблицы, которая состоит из миллионов записей, тогда ваш пакет будет медленным и размер журнала транзакций будет увеличиваться, и он может заполнить ваше полезное дисковое пространство.
Если вы отбросите ограничения, может случиться так, что вы пополните таблицу нечистыми данными и когда вы пытаетесь воссоздать ограничения, это может не позволить вам, поскольку это даст ошибки. поэтому убедитесь, что если вы отбросите ограничения, вы загружаете данные, которые правильно связаны друг с другом и удовлетворяют отношениям ограничений, которые вы собираетесь воссоздать.
поэтому, пожалуйста, внимательно подумайте о плюсах и минусах каждого метода и используйте его в соответствии с вашими требованиями.
Отключите все индексы (включая pk, который отключит все fks), затем снова включите pks.
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON [' + t.[name] + '] DISABLE;'+CHAR(13)
from
sys.tables t
where type='u'
select @sql = @sql +
'ALTER INDEX ' + i.[name] + ' ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from
sys.key_constraints i
join
sys.tables t on i.parent_object_id=t.object_id
where
i.type='PK'
exec dbo.sp_executesql @sql;
go
[Загрузите ваши данные]
Тогда верни все к жизни...
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from
sys.tables t
where type='u'
exec dbo.sp_executesql @sql;
go
Если вы используете схемы базы данных, отличные от ".dbo", или ваша БД содержит Pk, которые состоят из нескольких полей, пожалуйста, не используйте решение Carter Medlin, иначе вы повредите свою БД !!!
Когда вы работаете с разными схемами, попробуйте это (не забудьте сделать резервную копию вашей базы данных раньше!):
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+ '['+ t.[name] + '] DISABLE;'+CHAR(13)
from
sys.tables t
where type='u'
select @sql = @sql +
'ALTER INDEX ' + i.[name] + ' ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' + t.[name] + '] REBUILD;'+CHAR(13)
from
sys.key_constraints i
join
sys.tables t on i.parent_object_id=t.object_id
where i.type='PK'
exec dbo.sp_executesql @sql;
go
После выполнения некоторых действий без Fk вы можете переключиться обратно с помощью
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' + t.[name] + '] REBUILD;'+CHAR(13)
from
sys.tables t
where type='u'
print @sql
exec dbo.sp_executesql @sql;
exec sp_msforeachtable "ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL";