使用MSSQL2005，如果我首先截断子表(具有FK关系的主键的表)，我可以截断带有外键约束的表吗?
我知道我也可以
使用不带where子句的DELETE，然后RESEED标识(或) 删除FK，截断表，并重新创建FK。
我认为只要我在父表之前截断子表，我就可以不做上面的任何一个选项，但我得到了这个错误:
不能截断表'TableName'，因为它被一个FOREIGN KEY约束引用。
当前回答
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE table1;
TRUNCATE table2;
SET FOREIGN_KEY_CHECKS=1;
引用-截断外键约束表
在MYSQL为我工作
其他回答
唯一的方法是在截断之前删除外键。截断数据之后，必须重新创建索引。
下面的脚本生成删除所有外键约束所需的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
运行生成的脚本删除所有外键，截断表，然后运行生成的脚本重新创建所有外键。
查询从这里开始。
我写了以下方法，并尝试将它们参数化，这样你就可以在查询文档中运行它们，或者轻松地使用它们制作一个有用的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来禁用或启用约束
删除然后重置自动增量:
delete from tablename;
then
ALTER TABLE tablename AUTO_INCREMENT = 1;
如果这些答案都不像我的情况那样有效，那就这样做:
减少约束 将所有值设置为允许为空 截断表 添加已删除的约束。
好运！
如果不删除约束，就不能截断表。disable也不行。你需要放下一切。我已经做了一个脚本，删除所有约束，然后重新创建。
请确保将其包装在事务中;)
SET NOCOUNT ON
GO
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--DROP CONSTRAINT:
DECLARE @dynSQL varchar(MAX);
DECLARE cur CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
'
FROM
@table
OPEN cur
FETCH cur into @dynSQL
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@dynSQL)
print @dynSQL
FETCH cur into @dynSQL
END
CLOSE cur
DEALLOCATE cur
---------------------
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
truncate table your_table
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
---------------------
--ADD CONSTRAINT:
DECLARE cur2 CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
'
FROM
@table
OPEN cur2
FETCH cur2 into @dynSQL
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@dynSQL)
print @dynSQL
FETCH cur2 into @dynSQL
END
CLOSE cur2
DEALLOCATE cur2
推荐文章
- 在SQL Server上使用varchar(MAX) vs TEXT
- Visual Studio: ContextSwitchDeadlock
- Sql Server字符串到日期的转换
- 如何将SQL Azure数据库复制到本地开发服务器?
- SQL Server 2008不能用新创建的用户登录
- 用CSS截断长字符串:可行吗?
- SQL Server中User和Login的区别
- 在参数声明中，varchar(MAX)的大小是多少?
- 数据库性能调优有哪些资源?
- 为两列的组合添加唯一的约束
- SQL Server的隐藏特性
- 方括号[]在sql语句中有什么用?
- 对象'DF__*'依赖于列'*' -将int改为double
- 如何根据出生日期和getDate()计算年龄(以年为单位)
- 最有效的T-SQL方法垫一个varchar的左边到一定的长度?
