使用MSSQL2005,如果我首先截断子表(具有FK关系的主键的表),我可以截断带有外键约束的表吗?
我知道我也可以
使用不带where子句的DELETE,然后RESEED标识(或) 删除FK,截断表,并重新创建FK。
我认为只要我在父表之前截断子表,我就可以不做上面的任何一个选项,但我得到了这个错误:
不能截断表'TableName',因为它被一个FOREIGN KEY约束引用。
使用MSSQL2005,如果我首先截断子表(具有FK关系的主键的表),我可以截断带有外键约束的表吗?
我知道我也可以
使用不带where子句的DELETE,然后RESEED标识(或) 删除FK,截断表,并重新创建FK。
我认为只要我在父表之前截断子表,我就可以不做上面的任何一个选项,但我得到了这个错误:
不能截断表'TableName',因为它被一个FOREIGN KEY约束引用。
当前回答
我写了以下方法,并尝试将它们参数化,这样你就可以在查询文档中运行它们,或者轻松地使用它们制作一个有用的SP。
一)删除
如果你的表没有数百万条记录,这工作得很好,没有任何Alter命令:
---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb' --< Db Name
DECLARE @Schema AS NVARCHAR(30) = 'dbo' --< Schema
DECLARE @TableName AS NVARCHAR(30) = 'Book' --< Table Name
------------------ /Just Fill Parameters Value ----------------
DECLARE @Query AS NVARCHAR(500) = 'Delete FROM ' + @TableName
EXECUTE sp_executesql @Query
SET @Query=@DbName+'.'+@Schema+'.'+@TableName
DBCC CHECKIDENT (@Query,RESEED, 0)
在我上面的回答中,解决问题中提到的问题的方法是基于@s15199d的回答。
B)截断
如果你的表有数百万条记录,或者你在你的代码中没有任何Alter命令的问题,那么使用这个:
-- Book Student
--
-- | BookId | Field1 | | StudentId | BookId |
-- --------------------- ------------------------
-- | 1 | A | | 2 | 1 |
-- | 2 | B | | 1 | 1 |
-- | 3 | C | | 2 | 3 |
---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb'
DECLARE @Schema AS NVARCHAR(30) = 'dbo'
DECLARE @TableName_ToTruncate AS NVARCHAR(30) = 'Book'
DECLARE @TableName_OfOwnerOfConstraint AS NVARCHAR(30) = 'Student' --< Decelations About FK_Book_Constraint
DECLARE @Ref_ColumnName_In_TableName_ToTruncate AS NVARCHAR(30) = 'BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ColumnName_In_TableOfOwnerOfConstraint AS NVARCHAR(30) = 'Fk_BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ConstraintName AS NVARCHAR(30) = 'FK_Book_Constraint' --< Decelations About FK_Book_Constraint
------------------ /Just Fill Parameters Value ----------------
DECLARE @Query AS NVARCHAR(2000)
SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' DROP CONSTRAINT '+@FK_ConstraintName
EXECUTE sp_executesql @Query
SET @Query= 'Truncate Table '+ @TableName_ToTruncate
EXECUTE sp_executesql @Query
SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' ADD CONSTRAINT '+@FK_ConstraintName+' FOREIGN KEY('+@FK_ColumnName_In_TableOfOwnerOfConstraint+') REFERENCES '+@TableName_ToTruncate+'('+@Ref_ColumnName_In_TableName_ToTruncate+')'
EXECUTE sp_executesql @Query
在上面的回答中,我解决问题中提到的问题的方法是基于@LauroWolffValenteSobrinho的回答。 如果你有不止一个约束,那么你应该把它的代码像我一样附加到上面的查询 此外,您还可以更改上面的代码基@SerjSagan answer来禁用或启用约束
其他回答
正确的;你不能截断一个有FK约束的表。
通常我的处理方法是:
去掉约束 截断表格 重新创建约束。
(当然,这一切都在一笔交易中。)
当然,这只适用于子节点已经被截断的情况。否则,我会走不同的路线,完全取决于我的数据是什么样子的。(变量太多,不便在此赘述。)
最初的海报决定了为什么会这样;更多细节请看这个答案。
@denver_citizen和@Peter Szanto的回答对我不太适用,但我修改了它们,以解释:
组合键 “删除”和“更新”动作 重新添加时检查索引 dbo以外的模式 同时处理多个表
DECLARE @Debug bit = 0;
-- List of tables to truncate
select
SchemaName, Name
into #tables
from (values
('schema', 'table')
,('schema2', 'table2')
) as X(SchemaName, Name)
BEGIN TRANSACTION TruncateTrans;
with foreignKeys AS (
SELECT
SCHEMA_NAME(fk.schema_id) as SchemaName
,fk.Name as ConstraintName
,OBJECT_NAME(fk.parent_object_id) as TableName
,SCHEMA_NAME(t.SCHEMA_ID) as ReferencedSchemaName
,OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName
,fc.constraint_column_id
,COL_NAME(fk.parent_object_id, fc.parent_column_id) AS ColumnName
,COL_NAME(fk.referenced_object_id, fc.referenced_column_id) as ReferencedColumnName
,fk.delete_referential_action_desc
,fk.update_referential_action_desc
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fc
ON fk.object_id = fc.constraint_object_id
JOIN #tables tbl
ON OBJECT_NAME(fc.referenced_object_id) = tbl.Name
JOIN sys.tables t on OBJECT_NAME(t.object_id) = tbl.Name
and SCHEMA_NAME(t.schema_id) = tbl.SchemaName
and t.OBJECT_ID = fc.referenced_object_id
)
select
quotename(fk.ConstraintName) AS ConstraintName
,quotename(fk.SchemaName) + '.' + quotename(fk.TableName) AS TableName
,quotename(fk.ReferencedSchemaName) + '.' + quotename(fk.ReferencedTableName) AS ReferencedTableName
,replace(fk.delete_referential_action_desc, '_', ' ') AS DeleteAction
,replace(fk.update_referential_action_desc, '_', ' ') AS UpdateAction
,STUFF((
SELECT ',' + quotename(fk2.ColumnName)
FROM foreignKeys fk2
WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
ORDER BY fk2.constraint_column_id
FOR XML PATH('')
),1,1,'') AS ColumnNames
,STUFF((
SELECT ',' + quotename(fk2.ReferencedColumnName)
FROM foreignKeys fk2
WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
ORDER BY fk2.constraint_column_id
FOR XML PATH('')
),1,1,'') AS ReferencedColumnNames
into #FKs
from foreignKeys fk
GROUP BY fk.SchemaName, fk.ConstraintName, fk.TableName, fk.ReferencedSchemaName, fk.ReferencedTableName, fk.delete_referential_action_desc, fk.update_referential_action_desc
-- Drop FKs
select
identity(int,1,1) as ID,
'ALTER TABLE ' + fk.TableName + ' DROP CONSTRAINT ' + fk.ConstraintName AS script
into #scripts
from #FKs fk
-- Truncate
insert into #scripts
select distinct
'TRUNCATE TABLE ' + quotename(tbl.SchemaName) + '.' + quotename(tbl.Name) AS script
from #tables tbl
-- Recreate
insert into #scripts
select
'ALTER TABLE ' + fk.TableName +
' WITH CHECK ADD CONSTRAINT ' + fk.ConstraintName +
' FOREIGN KEY ('+ fk.ColumnNames +')' +
' REFERENCES ' + fk.ReferencedTableName +' ('+ fk.ReferencedColumnNames +')' +
' ON DELETE ' + fk.DeleteAction COLLATE Latin1_General_CI_AS_KS_WS + ' ON UPDATE ' + fk.UpdateAction COLLATE Latin1_General_CI_AS_KS_WS AS script
from #FKs fk
DECLARE @script nvarchar(MAX);
DECLARE curScripts CURSOR FOR
select script
from #scripts
order by ID
OPEN curScripts
WHILE 1=1 BEGIN
FETCH NEXT FROM curScripts INTO @script
IF @@FETCH_STATUS != 0 BREAK;
print @script;
IF @Debug = 0
EXEC (@script);
END
CLOSE curScripts
DEALLOCATE curScripts
drop table #scripts
drop table #FKs
drop table #tables
COMMIT TRANSACTION TruncateTrans;
因为TRUNCATE TABLE是一个DDL命令,所以它不能检查表中的记录是否被子表中的记录引用。
这就是为什么DELETE有效而TRUNCATE TABLE无效的原因:因为数据库能够确保它没有被另一条记录引用。
我刚刚发现,只要先禁用子表上的外键约束,就可以在父表上使用TRUNCATE表。 如。
子表上的外键约束child_par_ref引用PARENT_TABLE
ALTER TABLE CHILD_TABLE DISABLE CONSTRAINT child_par_ref;
TRUNCATE TABLE CHILD_TABLE;
TRUNCATE TABLE PARENT_TABLE;
ALTER TABLE CHILD_TABLE ENABLE CONSTRAINT child_par_ref;
截断不适合我,删除+重新播种是最好的方法。 如果你们中的一些人需要遍历大量的表来执行delete + reseed,你可能会遇到一些没有标识列的表的问题,下面的代码在尝试重新播种之前检查标识列是否存在
EXEC ('DELETE FROM [schemaName].[tableName]')
IF EXISTS (Select * from sys.identity_columns where object_name(object_id) = 'tableName')
BEGIN
EXEC ('DBCC CHECKIDENT ([schemaName.tableName], RESEED, 0)')
END