外部キーが設定されているとTRUNCATEできない #MariaDB #MySQL

inabaです。

最近はgolangでDBをいじっています。
テストもテスト用DBを作り、実際に書き込んで行うようにしていました。

テストをするたびにAUTO_INCREMENTの値が増えていく

まぁそうでしょう。
なので、テストデータのクリアをするためにTRUNCATEしようと思ってエラー出てハマりました。

エラー

2つのエラーが出ました。

プレースホルダ

_, err = db.Exec("TRUNCATE TABLE ?", tableName)

上記コードの時は下記のエラーが出ました。

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1

テーブル名にプレースホルダは使えないらしい。
元々プレースホルダはキャッシュ目的だからFROM句は変えないですし、
セキュリティ的な使用でも、ユーザの入力をFROM句には使わないから妥当な動きですよね。

外部キー制約

プレースホルダをやめてfmt.Sprintfを使ってやるようにしてみたらエラー内容が変わりました。

_, err = db.Exec(fmt.Sprintf("TRUNCATE TABLE %s", tableName))

下記がエラー。

Error 1701: Cannot truncate a table referenced in a foreign key constraint ...

これは、外部キー制約によってTRUNCATEができないというエラーでした。

外部キー制約の回避

外部キー制約を回避する方法があります。

_, err = db.Exec("SET FOREIGN_KEY_CHECKS = 0")
_, err = db.Exec(fmt.Sprintf("TRUNCATE TABLE %s", tableName))
_, err = db.Exec("SET FOREIGN_KEY_CHECKS = 1")

上記コードではエラーは出ません。
FOREIGN_KEY_CHECKSを0に設定すると、外部キー制約の回避ができます。

別の解決法

AUTO_INCREMENTが初期化されればよいのであれば、DELETEしてからAUTO_INCREMENTをリセットすることもできます。

_, err = db.Exec(fmt.Sprintf("DELETE FROM %s", tableName))
_, err = db.Exec(fmt.Sprintf("ALTER TABLE %s AUTO_INCREMENT = 1", tableName))

これなら2行で済みます。
ただ、子テーブルにデータが入っているとエラーになるので、テストで子テーブルも使っている場合は子テーブルの方から消していってください。

今日はここまで。