android - SQLite 主键自动增量不起作用

Question

我正在尝试创建一个带有自动递增主键的表。包含建表的SQL查询。
问题是自动增量不起作用。这意味着当我插入一行 NULL 作为 conversation_id 的值时，它只会插入 null。我在多个表上都有这个问题。

-- Table: conversations
CREATE TABLE conversations (
    conversation_id INTEGER (64) PRIMARY KEY
                                 UNIQUE,
    target_id       BIGINT (64),
    sender_id       BIGINT (64),
    status          STRING (16)  NOT NULL
                                 DEFAULT unseen,
    is_group        INTEGER (1)  NOT NULL
                                 DEFAULT (0),
    last_update     INTEGER (32) DEFAULT (0),
    target_name     STRING (64),
    target_photo    STRING (256),
    unread_count    INTEGER (10) DEFAULT (0),
    last_message    STRING (256) 
);

下面是我插入表格的方法:

public Conversation addConversation(Conversation conversation) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues row = new ContentValues();

        row.put("target_id", conversation.getTargetID());
        row.put("sender_id", conversation.getSenderID());
        row.put("target_name", conversation.getTargetName());
        row.put("target_photo", conversation.getTargetPhoto());
        row.put("status", conversation.getStatus());
        row.put("unread_count", conversation.getUnreadCount());
        row.put("last_message", conversation.getLastMessage());

        conversation.setConversationID(db.insert(TBL_CONVERSATIONS, null, row));
        Log.d(TAG, "conversation added: "+conversation.getConversationID());

        db.close();

        return conversation;
    }

奇怪的是，当我从 insert 方法中检索插入 ID 时，它返回了正确的值，但实际的数据库字段为空。

如果我理解正确，声明为 INTEGER PRIMARY KEY 的列将自动递增。 [Cite]

Answer 1

来自文档:

A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL.

您不必在具有 PRIMARY KEY 约束的 COLUMN 上添加 UNIQUE 约束。
说明:

A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints.

而是添加 NOT NULL。这就是为什么:

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns.

我建议使用此列定义:

CREATE TABLE conversations (
    conversation_id INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT,
...
}