使用MSSQL2005，如果我首先截断子表(具有FK关系的主键的表)，我可以截断带有外键约束的表吗?
我知道我也可以
使用不带where子句的DELETE，然后RESEED标识(或) 删除FK，截断表，并重新创建FK。
我认为只要我在父表之前截断子表，我就可以不做上面的任何一个选项，但我得到了这个错误:
不能截断表'TableName'，因为它被一个FOREIGN KEY约束引用。
当前回答
下面是我写的一个脚本，以使这个过程自动化。我希望这能有所帮助。
SET NOCOUNT ON
-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)
DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)
-- 1 = Will not execute statements
SET @Debug = 0
-- 0 = Will not create or truncate storage table
-- 1 = Will create or truncate storage table
SET @Recycle = 0
-- 1 = Will print a message on every step
set @Verbose = 1
SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'
-- Drop Temporary tables
DROP TABLE #FKs
-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
OBJECT_NAME(constraint_object_id) as ConstraintName,
OBJECT_NAME(parent_object_id) as TableName,
clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
clm2.name as ReferencedColumnName
INTO #FKs
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1
ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_id
JOIN sys.columns clm2
ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_id
WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
ORDER BY OBJECT_NAME(parent_object_id)
-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
IF @Verbose = 1
PRINT '1. Creating Process Specific Tables...'
-- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
CREATE TABLE [Internal_FK_Definition_Storage]
(
ID int not null identity(1,1) primary key,
FK_Name varchar(250) not null,
FK_CreationStatement varchar(max) not null,
FK_DestructionStatement varchar(max) not null,
Table_TruncationStatement varchar(max) not null
)
END
ELSE
BEGIN
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '1. Truncating Process Specific Tables...'
-- TRUNCATE TABLE IF IT ALREADY EXISTS
TRUNCATE TABLE [Internal_FK_Definition_Storage]
END
ELSE
PRINT '1. Process specific table will be recycled from previous execution...'
END
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '2. Backing up Foreign Key Definitions...'
-- Fetch and persist FKs
WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
BEGIN
SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)
SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)
INSERT INTO [Internal_FK_Definition_Storage]
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'
END
END
ELSE
PRINT '2. Backup up was recycled from previous execution...'
IF @Verbose = 1
PRINT '3. Dropping Foreign Keys...'
-- DROP FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Dropping [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '4. Truncating Tables...'
-- TRUNCATE TABLES
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > ' + @Statement
END
IF @Verbose = 1
PRINT '5. Re-creating Foreign Keys...'
-- CREATE FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Re-creating [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '6. Process Completed'
其他回答
对于MS SQL，至少在更新的版本中，你可以通过如下代码禁用约束:
ALTER TABLE Orders
NOCHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO
TRUNCATE TABLE Customers
GO
ALTER TABLE Orders
WITH CHECK CHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO
如果我理解正确的话，您要做的是为涉及集成测试的DB设置一个干净的环境。
我在这里的方法是放弃整个模式，稍后再重新创建它。
原因:
您可能已经有了“创建模式”脚本。重用它进行测试隔离很容易。 创建模式非常快。 使用这种方法，可以很容易地设置脚本，让每个fixture创建一个NEW模式(使用临时名称)，然后开始并行运行测试fixture，使测试套件中最慢的部分更快。
如果您以任何频率执行此操作，甚至在时间表上，我绝对不会明确地使用DML语句。写入事务日志的成本非常高，将整个数据库设置为SIMPLE恢复模式以截断一个表是荒谬的。
最好的办法，不幸是艰苦或费力的办法。这是:
减少约束 截断表 重新创建约束
我这样做的过程包括以下步骤:
在SSMS中，右键单击有问题的表，并选择查看依赖项 记下引用的表格(如果有的话) 回到对象资源管理器，展开Keys节点并记下外键(如果有的话) 开始编写脚本(删除/截断/重新创建)
这种性质的脚本应该在begin tran和commit tran块中完成。
唯一的方法是在截断之前删除外键。截断数据之后，必须重新创建索引。
下面的脚本生成删除所有外键约束所需的SQL。
DECLARE @drop NVARCHAR(MAX) = N'';
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id];
SELECT @drop
接下来，下面的脚本生成重新创建外键所需的SQL。
DECLARE @create NVARCHAR(MAX) = N'';
SELECT @create += N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
+ ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the columns in the constraint table
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(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
+ '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the referenced columns
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(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs
ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;
SELECT @create
运行生成的脚本删除所有外键，截断表，然后运行生成的脚本重新创建所有外键。
查询从这里开始。
最简单的方法: 1 -在phpmyadmin中输入 2 -点击左列的表名 3 -点击操作(顶部菜单) 4 -点击“清空表格(TRUNCATE)” 5 -禁用框“启用外键检查” 6 -完成!
图片教程链接 教程:http://www.imageno.com/wz6gv1wuqajrpic.html (对不起，我没有足够的声誉上传图片:P)
推荐文章
- 在SQL中更新多个列
- 如何删除表中特定列的第一个字符?
- 等价的限制和偏移SQL Server?
- 如何从SQL Server中的字符串中剥离所有非字母字符?
- 为什么我不能在DELETE语句中使用别名?
- 多语句表值函数vs内联表值函数
- NOLOCK提示在SELECT语句中的作用
- SQL OVER()子句-它什么时候有用，为什么有用?
- 检查SQL Server登录是否已经存在
- 我如何使用ROW_NUMBER()?
- SQL或者TSQL是图灵完备的吗?
- 如何检查表上持有哪些锁
- SQL Server数据库备份恢复到低版本
- SQL Server:过滤sp_who2的输出
- 在SQL Server上使用varchar(MAX) vs TEXT
