主键重置问题与外键和删除 [英] Primary Key Resetting Issue with foreign keys and delete

问题描述

我有一组表，这些表将清除并上传新数据.其中之一，Person带有指向它的外键，即使其他表为空，它也阻止了我使用TRUNCATE Table.

I have a set of tables which I am going to clear out and upload new data into. One of these, Person has foreign keys pointing to it which prevent me from using TRUNCATE Table even though the other tables are empty.

在关闭外键检查后，我使用了DELETE FROM来解决此问题.除插入新值时它们从旧值开始，并且我需要将它们重置以再次从1开始(或至少一些一致的可预测值)之外，此方法有效.

I have used DELETE FROM after turning off the foreign key checks to get around this. This works except when I insert new values they start at the old value going up and I need them to reset to start at 1 again (or at least some consistent predictable value)

DBCC CHECKIDENT([Person]，RESEED，-1)；或DBCC CHECKIDENT([Person]，RESEED，0)；我在其他地方看到过建议重设身份的建议，但没有给我有用的结果，而是产生了以下结果:

DBCC CHECKIDENT ([Person], RESEED, -1); or DBCC CHECKIDENT ([Person], RESEED, 0); I have seen suggested on other places for resetting the identity but give no useful results for me instead yielding:

Checking identity information: current identity value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

我该如何截断以使用指向表的外键或使用Delete清除表以重置主键的自动增量并从1开始?

How can I either get truncate to work with the foreign keys pointing to my table OR using delete to clear the table get the primary key's auto increment to reset and start at 1?

推荐答案

如果有指向该表的外键，则不能截断该表. 一种方法是暂时删除外键，并在截断后重新创建它们.

You cannot truncate a table, if there are foreign keys pointing to it. One way would be, to temporarily drop the foreign keys and after truncating recreate them.

禁用外键约束，仍然可以截表吗? (SQL Server 2005)

这篇关于主键重置问题与外键和删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！