【sqlite3】create table asってそういう仕様だったんですね・・・

SQLite3でテーブルをコピーする方法の一つ、create table as構文にはちょっとした罠があります。
「create table 新しいテーブル名 as select * from 元となるテーブル名」

これで「元となるテーブル」と同じスキーマーの「新しいテーブル」が作成され、さらにデータもコピーできると思い込んでいました。

主キーが設定された元テーブルを、create table asでコピーしました。「新しいテーブル」には、primary keyが存在しませんでした。

SQLite3ではprimary keyはcreate tableのみ設定することができます。ALTER TABLE構文では対応していません😭

【結論】primary keyが必要なテーブルは、create table asを使ってはいけない

同一データベースファイル中であれば、ALTER TABLEで名前を変えるのが一番手っ取り早いです。

attach databaseで別のデータベースファイルにあるテーブルのコピー手段として、create table asを利用している場合、対策案は、2つあります。

1. create tableで作り直して、コピーする。
   create table 新しいテーブル( pk primary key … )
   insert into 新しいテーブル select * from 元となるテーブル

   テーブルのスキーマーは、以下SQLで取得できます。
   SELECT sql FROM SQLITE_MASTER WHERE name=’元となるテーブル’

2. dumpを使って、create＆コピーする
   sqlite3 元.sqlite3 “.dump 元となるテーブル” | sqlite3 運用.sqlite3
   → 元となるテーブルがそのままの状態で運用.sqlite3にコピーされます。
   sqlite3 運用.sqlite3 “alter table 元となるテーブル rename to 新しいテーブル”
   → table名を変更します。

   運用.sqlite3に「元となるテーブル」名と同名がある場合は、上記を応用し、一時的なsqlite3データベースを作成して、経由することで別名を実現可能です。

create table as でprimary keyをそのままコピーして欲しい、primary keyを後から設定できるようにしてほしいですね。