【SQL】整数型のPRIMARY KEYを自前で発行する際の注意点

MSSQLやSQLiteなどのリレーショナルデータベースで整数型のPRIMARY KEYを自前で発行する際の注意点です。

概要

MSSQLなら「IDENTITYの指定」を使ってやるべきかもですが、自前で実装する場合の注意点になります。

注意点

SQLでCOUNTする場合

using (var connection = new SqliteConnection(DataSource))
{
    int cnt;

    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(*) FROM TABLE_NAME";

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
        {

        cnt = (int)reader.GetValue(0);
    }
}

COUNTする場合の注意点は、もしIDがユニークになっていて、IDは歯抜け（例:100まで発行されてて途中32とかに欠番）がある場合、エラーになってしまう。

COUNT関数は行数を返すが、既に100まで発行してて、次に101を発行したいとしても、何らかの理由で例えば欠番が1つあるとすると、上記のコードでは重複する100番を再度発行しようとしてエラーになる。

これを回避するにはMAX関数を使うが、その場合にも注意点がある。

SQLでMAXする場合

using (var connection = new SqliteConnection(DataSource))
{
    int cnt;

    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT MAX(ID) FROM TABLE_NAME";

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
        {

        cnt = (reader.GetValue(0) == System.DBNull.Value ? 0 : (int)reader.GetValue(0));
    }
}

MAX関数は列の最大値を返す。なので歯抜けがあっても前節の例だと101が発行される。

注意点は、まだ行が存在しないテーブルの場合はNULLは返ってくるので園対応をする必要がある。

まとめ（感想文）

黙って「IDENTITYの指定」の設定するのが吉。