使用MSSQL2005,如果我首先截断子表(具有FK关系的主键的表),我可以截断带有外键约束的表吗?
我知道我也可以
使用不带where子句的DELETE,然后RESEED标识(或) 删除FK,截断表,并重新创建FK。
我认为只要我在父表之前截断子表,我就可以不做上面的任何一个选项,但我得到了这个错误:
不能截断表'TableName',因为它被一个FOREIGN KEY约束引用。
使用MSSQL2005,如果我首先截断子表(具有FK关系的主键的表),我可以截断带有外键约束的表吗?
我知道我也可以
使用不带where子句的DELETE,然后RESEED标识(或) 删除FK,截断表,并重新创建FK。
我认为只要我在父表之前截断子表,我就可以不做上面的任何一个选项,但我得到了这个错误:
不能截断表'TableName',因为它被一个FOREIGN KEY约束引用。
当前回答
如果不删除约束,就不能截断表。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
其他回答
DELETE FROM TABLENAME
DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME', RESEED, 0)
注意,如果您有数百万条以上的记录,这可能不是您想要的,因为它非常慢。
正确的;你不能截断一个有FK约束的表。
通常我的处理方法是:
去掉约束 截断表格 重新创建约束。
(当然,这一切都在一笔交易中。)
当然,这只适用于子节点已经被截断的情况。否则,我会走不同的路线,完全取决于我的数据是什么样子的。(变量太多,不便在此赘述。)
最初的海报决定了为什么会这样;更多细节请看这个答案。
在使用delete语句删除表中的所有行之后,使用以下命令
delete from tablename
DBCC CHECKIDENT ('tablename', RESEED, 0)
编辑:修正的SQL Server语法
截断不适合我,删除+重新播种是最好的方法。 如果你们中的一些人需要遍历大量的表来执行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
如果这些答案都不像我的情况那样有效,那就这样做:
减少约束 将所有值设置为允许为空 截断表 添加已删除的约束。
好运!