使用 `TRUNCATE` 清空 TABLE 时报如下错误:
> 无法截断表 'TABLE_NAME',因为该表正由 FOREIGN KEY 约束引用。
## SQL Server 对策
### 案1
1. 删除数据;
2. 重置自增的种子序列(如果有自增字段的话);
```sql
DELETE FROM [TABLE_NAME]
DBCC CHECKIDENT ('TABLE_NAME', RESEED, 0)
```
### 案2
1. 删除外键约束;
2. 执行 `TRUNCATE`;
3. 增加外键约束;
```sql
BEGIN TRAN
ALTER TABLE [TABLE_NAME] DROP CONSTRAINT [FK_KEY_NAME]
TRUNCATE TABLE [TABLE_NAME]
ALTER TABLE [TABLE_NAME] WITH NOCHECK ADD CONSTRAINT [FK_KEY_NAME] FOREIGN KEY([COLUMN_NAME]) REFERENCES [ANOTHER_TABLE_NAME] ([COLUMN_NAME])
COMMIT
```
## MySQL 对策
### 案1
1. 禁用外键检查;
2. 执行 `TRUNCATE`;
3. 恢复外键检查;
```sql
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE_NAME;
TRUNCATE ANOTHER_TABLE_NAME;
SET FOREIGN_KEY_CHECKS = 1;
```
### 案2
1. 删除数据;
2. 重置自增的种子序列(如果有自增字段的话);
```sql
DELETE FROM TABLE_NAME;
ALTER TABLE TABLE_NAME AUTO_INCREMENT = 1;
```
### 案3
1. 删除外键约束;
2. 执行 `TRUNCATE`;
3. 增加外键约束;
```sql
START TRANSACTION;
ALTER TABLE
TRUNCATE
TRUNCATE ;
ALTER TABLE
COMMIT;
```
## 参考
1. [Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?](https://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint)
1. [How to truncate a foreign key constrained table?](https://stackoverflow.com/questions/5452760/how-to-truncate-a-foreign-key-constrained-table)
1. [Alter table to add the foreign](http://www.java2s.com/Tutorial/MySQL/0080__Table/Altertabletoaddtheforeignkey.htm)