SQLiteのAUTOINCREMENTの落とし穴

SQLiteで躓いた時の発見から。MySQLのCREATE TABLEを使いまわせる書き方のように見えたのですが・・・

接続と、テーブル一覧表示部分

成否だけでは確認も難があるので、まずは接続と、テーブル一覧の出力です。

接続部分

try{
 $pdo = new PDO(
 "sqlite:".dirname(__FILE__).'/dbfiles/sample.sqlite3',
 null,
 null,
 array(
 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
 ,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
 )
 );
}catch (PDOException $e) {
 echo 'Connection failed: ' . $e->getMessage()."\n";
 exit;
}

テーブル一覧部分

 $sql = "SELECT name,sql FROM `sqlite_master`";
 $result = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

 echo "<h2>テーブル一覧</h2>";
 echo "<pre>";
 var_dump($result);
 echo "</pre>";
 echo "<hr>\n";

テーブルを作ってみる

INTで書くと失敗

AUTOINCREMENTと書かなければならないことに要注意である点、派生的な型もINT等はINTEGER、VARCHARなどはTEXTとして扱ってくれるという前提知識があったので、MySQLとの流用前提の型名で書いてみたのですが・・・

    $sql = "CREATE TABLE IF NOT EXISTS `sample`
            (
             `num` INT PRIMARY KEY AUTOINCREMENT
            ,`name` VARCHAR(50)
            ,`age` INT
            )";
    $result = $pdo->exec($sql);

SQLSTATE[HY000]: General error: 1 AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

結果はエラー。PRIMARY KEYにできるのはINTEGERですが、INTEGER扱いされた別の型名は対象外のようです。

INTEGERにすると成功

まずは素直に当該列だけINTEGERにすると無事動きました。

    $sql = "CREATE TABLE IF NOT EXISTS `sample0`
            (
             `num` INTEGER PRIMARY KEY AUTOINCREMENT
            ,`name` VARCHAR(50)
            ,`age` INT
            )";
    $result = $pdo->exec($sql);

INTと後置にするとエラーになったが？

MySQLの場合、型によってプライマリキー設定を後置しないといけない場合があるので、もしかして後置すれば変換結果で判定してくれたりしないか？ということで、INTに戻し、後置で試しました。

    $sql = "CREATE TABLE IF NOT EXISTS `sample`
            (
             `num` INT AUTOINCREMENT
            ,`name` VARCHAR(50)
            ,`age` INT
            , PRIMARY KEY  (`num`)
            )";
    $result = $pdo->exec($sql);

SQLSTATE[HY000]: General error: 1 near “AUTOINCREMENT”: syntax error

エラーなのですが内容は違い、なぜかAUTOINCREMENTに問題があるとの結果です。なお、ちゃんとINTEGERにしてもアウトです。

INTとAUTO_INCREMENTでも作成成功

まさかとは思いつつ、AUTO_INCREMENTにしてみます。すると・・・

    $sql = "CREATE TABLE IF NOT EXISTS `sample`
            (
             `num` INT AUTO_INCREMENT
            ,`name` VARCHAR(50)
            ,`age` INT
            , PRIMARY KEY  (`num`)
            )";
    $result = $pdo->exec($sql);

エラーにならずにテーブル作成に成功しました。

MySQLの時もプライマリキー後に書くよう癖をつけておくと、SQLiteでもそのまま使える、と思ったのですが・・・

実際にINSERTしてみると

ランダムINSERTとテーブル表示部分

 $tbl_name = "sample";
//INSERT
 $in_name = "user-".rand(1000,9999);
 $in_age = rand(18,60);

 $sql = "INSERT INTO `{$tbl_name}`
 (`name`, `age`) VALUES(:name, :age)";
 $stmt = $pdo->prepare($sql);//実行・結果取得
 $stmt->bindValue(':name', $in_name, PDO::PARAM_STR);
 $stmt->bindValue(':age', $in_age, PDO::PARAM_INT);
 $stmt->execute();

 //テーブル内容表示----------
 $sql = "SELECT * FROM `{$tbl_name}`";
 $table_data = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}
?>
<h2>テーブル「<?php echo $tbl_name?>」の内容</h2>
<table>
<tr><th>NUMBER</th><th>NAME</th><th>AGE</th></tr>
<?php foreach($table_data as $row):?>
<tr>
 <td><?php echo $row['num'];?></td>
 <td><?php echo $row['name'];?></td>
 <td><?php echo $row['age'];?></td>
</tr>
<?php endforeach;?>

結果は

作成時エラーになっていなかったものの、numはAUTO INCREMENTされていませんでした。

「後ろに置くとAUTO_INCREMENTにしないとエラーがでる」のは納得がいかない現象ですが、結局PRIMARY KEYの後置はできない、「INTEGER」とアンダーバー無しの「AUTOINCREMENT」でないといけない。という形で、都合よくSQL文を使いまわすことはできないようです。