刪除測試資料後,IDENTITY 欄位種子不會自動歸零。本文對比 TRUNCATE TABLE 與 DBCC CHECKIDENT,並說明無法清空資料時的替代方案。
TRUNCATE TABLE tableName;
WHERE。-- 重置為 1(下一筆插入將為 1)
DBCC CHECKIDENT ('tableName', RESEED, 0);
-- 重置為 100(下一筆插入將為 101)
DBCC CHECKIDENT ('tableName', RESEED, 100);
-- 僅檢查當前種子值(不修改)
DBCC CHECKIDENT ('tableName');
db_owner 權限。TRUNCATE TABLE(首選)。DBCC CHECKIDENT ('tbl', RESEED, 新種子)。ALTER TABLE child NOCHECK CONSTRAINT fk_name;TRUNCATE TABLE parent;ALTER TABLE child CHECK CONSTRAINT fk_name;SELECT * INTO new_tbl FROM old_tbl WHERE 1=0(複製結構含 IDENTITY)。INSERT INTO new_tbl (col...) SELECT col... FROM old_tbl;(不插入 IDENTITY 欄,自動重新編號)。sp_rename)。CREATE TABLE dbo.TestIdentity (Id INT IDENTITY(1,1) PRIMARY KEY, Val VARCHAR(10));
INSERT INTO dbo.TestIdentity (Val) VALUES ('A'),('B'),('C');
-- Id: 1,2,3
DELETE FROM dbo.TestIdentity; -- 種子仍為 4
INSERT INTO dbo.TestIdentity (Val) VALUES ('D'); -- Id=4
TRUNCATE TABLE dbo.TestIdentity; -- 種子歸 1
INSERT INTO dbo.TestIdentity (Val) VALUES ('E'); -- Id=1 ✓
-- 或用 DBCC
DBCC CHECKIDENT ('dbo.TestIdentity', RESEED, 0);
INSERT INTO dbo.TestIdentity (Val) VALUES ('F'); -- Id=1 ✓
| 資料庫 | 重置自增/序列 |
|---|---|
| MySQL | ALTER TABLE tbl AUTO_INCREMENT = 1; (需表空) |
| PostgreSQL | ALTER SEQUENCE seq_name RESTART WITH 1; |
| Oracle | DROP SEQUENCE seq; CREATE SEQUENCE seq START WITH 1; (12c+ Identity: ALTER TABLE ... MODIFY GENERATED AS IDENTITY (START WITH 1)) |