PHP：SQLiteでもAUTO_INCREMENTで書ける

SQLiteで躓いた時の発見からの紹介です。

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

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

接続部分

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に問題があるとの結果です。

後置の場合は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がちょっと楽になりそうです。