SQLiteのIntegerのカラムに、primary keyを指定した際の入力可能な値について

Question

こんにちは。
知見ある方いましたら、ご教授いただけますと幸いです。

前提

現在の仕事にて、Androidアプリ(java)を開発しております。
アプリ内部のDB(sqlite3)にて、以下のようなデータのテーブルが既存実装として存在しております。

既存仕様

DBについて

ユーザーID	データID
1234567	1
2345678	2

• ID : Integer primary key
  • アプリにてサインインしたユーザーのIDを格納している
• データID : Integer
  • サインインしたユーザーが使用する、他のDBに保存されている設定のどれを使用するか?を判断できるIDを格納している

補足

• アプリは、サインインして利用することが前提となっている
• サインイン完了時に、ユーザーIDをSharedPreferenceに保存している
• サインイン後に遷移できるある画面にて、上記DBへの保存が実施できる

修正したい問題

• アプリの仕様が変わり、サインインなしでもアプリの利用が可能になった。
• 未サインインの状態で、上記の上記DBへの保存が行える画面に遷移し、保存できるようになったため、永続時に必要なユーザーIDが存在しないパターンが発生する様になった
• そのため、保存を行うと現在は android.database.sqlite.SQLiteDatatypeMissmatchExceptionとなり、SQLiteDatabase#insert()に失敗している状態

問題に対する現在の修正案

• DBへのinsert()実行時、未サインイン(ユーザーIDが取得出来なかった)場合は、ダミーとなる -1をユーザーIDとして、保存を行う
• ※-1としている理由
  • サインインに成功している時のユーザーIDは必ず自然数となるため、重複を避けるため

うまくいっていない点

• ユーザーIDのカラムには、primary keyが指定されているためか、 -1を格納出来ない。
• SQLiteDatatypeMissmatchExceptionなどは発生しておらずSQLiteDatabase#insert()の戻り値が、失敗を意味する-1が必ず帰ってくる状態
• 暫定として、0、1を指定した場合は、insert出来た
• 暫定として、ユーザーIDカラムのprimary keyの指定をやめた場合、-1をinsert出来た

実現したいこと(知りたいこと)

• 暫定として、0、1を指定した場合は、insert出来た
• 暫定として、ユーザーIDカラムのprimary keyの指定をやめた場合、-1をinsert出来た

上記の動作は、Sqliteの仕様なのでしょうか?
調査を行ったのですが、明示的な仕様を見つけられず・・
ご教授いただけると幸いです。

また、その他-1を指定する以外の対応方法のアイディアをお持ちの方いましたら、
ご教授いただけますと幸いです。

該当のソースコード

業務上コードのため、省略させていただきます。。
概要としては以下の様な形です。

/**
** パラメータで、前述でいう「データID」を受け取ります。
*//
synchronizeed boolean hogehoge(int id) {
    ContentValues values = new ContentValues();
    // 前述でいう、「ユーザーID」を受け取りますが、未サインインのため、""が返ってくる様になってしまいました
    values .put("ユーザーIDのテーブル名", getUserID()); 
    values .put("データIDのテーブル名", id);
    try{
    // SQLiteDatabaseの生成処理は省略します

    long rowId = SQLiteDatabase.insert("テーブル名", null, values);
    // 上記rowIdで-1となり失敗する
    // getUserID()の箇所を-1に変更した場合も同様
    } finnay {
    }
}

補足情報（FW/ツールのバージョンなど）

• Android Studio 2021.2.1 Patch 2
• Android 12.0(エミュレータ Pixel 4)
• SQLite3

Comments

[jimbe]

なぜ「サインインしていないユーザ」のために、「サインインしたユーザーが使用する」データIDを保存しているデーブルを用いるのでしょうか。その辺から設計と合わなくなっているのでは。

Answer 1

暫定として、0、1を指定した場合は、insert出来た
暫定として、ユーザーIDカラムのprimary keyの指定をやめた場合、-1をinsert出来た

integer primary key の列は 隠し列である "ROWID" のエイリアスになりますので、ドキュメント的にはそちらのほうで値範囲が見られるかもしれません。

ROWID は insert の結果として返される値でもあり、 insert はご存知のように insert に失敗すると -1 を返します。
すなわち、 ROWID として -1 が許されると、 insert の戻り値で成功か失敗かの区別が付きません。
このことから、少なくとも -1 、多ければ負数全体が ROWID には入らないのではないでしょうか。

-1 を使う方向でというなら、 primary key では無く (ROWID と関係無くして ) unique と not null にするとかで良い気がします。

Comments

[OctNasu]

ご回答ありがとうございます。

> すなわち、 ROWID として -1 が許されると、 insert の戻り値で成功か失敗かの区別が付きません。

理解しました、確かに成功時も-1(insert成功した値)、失敗時(insertメソッドの戻り値)と帰ってくると、
区別がつかないですね・・

修正の調査方針としては、以下の二択になりそうかと理解致しました。

> -1 を使う方向でというなら、 primary key では無く (ROWID と関係無くして ) unique と not null にするとかで良い気がします。

こちらは「create table時の、primary keyの指定を取りやめる」ということになる理解でおりますが、
具体的には、マイグレーション等でテーブルの再作成などが必要になるということでしょうか?
誤った理解でいたらご指摘いただけると幸いです。

> integer primary key の列は 隠し列である "ROWID" のエイリアスになりますので、ドキュメント的にはそちらのほうで値範囲が見られるかもしれません。

こちらにつきましては、ROWIDのドキュメントで仕様について調査して、
追加可能な値について調査し、未サインイン時の値の判断として、使用出来そうなデータがないかみます。

[jimbe]

> マイグレーション等でテーブルの再作成などが必要になるということでしょうか?

primary key 列は alter table で修正など出来ないと思いますので、移行作業は必要と思います。

[OctNasu]

ご回答ありがとうございます。

> primary key 列は alter table で修正など出来ないと思いますので、移行作業は必要と思います。

分かりました、ありがとうございます。

また、ROWIDのドキュメントの方も色々と確認したところ、64 ビットの符号付き整数に対応しているとのことだった。

[5. ROWID と INTEGER PRIMARY KEY](https://www.sqlite.org/lang_createtable.html#rowid)

そのため、負数も対応しているかと思ったのですが、実働は-10などの-1以外の負数もやはりinsert出来ず・・
SQliteのバージョンなども疑い、
実際にネット上では負数をinsert出来たらしい人のバージョンに合わせたりもしたのですが、
ちょっと原因は分かりませんでした。

そのため、やはりテーブルの移行処理を行う方針で、検討していく予定です。

[jimbe]

rowid に対する制約は、 SQLite 自体の他に Android/Java と SQLite を結ぶドライバもしくはもっとアプリ寄りな機能による可能性もあります。
「insert メソッドが rowid を返す」というのは SQLiteDatabase の insert メソッドの仕様であって SQLite の INSERT ステートメントの仕様ではありませんので。
insert メソッドでは無く execSQL なら可能だったりがあるかもしれません。

[OctNasu]

ご回答ありがとうございます。

解決欄に記載したのですが、負数を登録できない挙動は私の実装確認ミスでした・・
お手数をおかけ致しました。

> rowid に対する制約は、 SQLite 自体の他に Android/Java と SQLite を結ぶドライバもしくはもっとアプリ寄りな機能による可能性もあります。
> 「insert メソッドが rowid を返す」というのは SQLiteDatabase の insert メソッドの仕様であって SQLite の INSERT ステートメントの仕様ではありませんので。
> insert メソッドでは無く execSQL なら可能だったりがあるかもしれません。

上記は、今後SQlite使用時の考慮点として控えさせていただきます。ありがとうございます。

・・また、負数を利用するということは好ましくないのではという点もあるかと思いますが、
今回のケースでは、私の上長と相談し、
DB作り直しではなく、現在の構成のまま負数を受け入れるという方針になりました。

Answer 2

ユーザーIDのカラムには、primary keyが指定されているためか、 -1を格納出来ない。

こちらは、私の既存実装の確認のミスでした・・
既存実装は、insertメソッド実行時の戻り値が、0以上の場合のみsetTransactionSuccessful()を呼ぶ処理となっておりました。
そのため、-1のinsertに成功しても、上記処理が呼ばれずにトランザクション終了するため、本質問事項の挙動となっておりました。

上記を確認できたため、本質問は解決済みとさせていただきます。