SQLite的主键自动增加不起作用 [英] SQLite PRIMARY key AutoIncrement doesn't work

问题描述

我想要一个有一个表有自动递增的主键。为表创建SQL查询包括在内。
问题是自动递增无法正常工作。当我插入一排NULL作为 conversation_id 的价值，它只是插入空含义。我有多个表这个问题。

I'm trying to a have a table with an auto incremented primary key. The SQL query for table creation is included.
Problem is the auto-increment does not work. Meaning when I insert a row with NULL as the value of conversation_id it just inserts null. I have this problem on multiple tables.

-- 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) 
);

以下是我使用插入到表中的方法：

The following is the method I use to insert into table:

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;
    }

这里的奇怪的事情是，当我检索插入ID插入方法，它返回正确的值，但实际的数据库字段为空。

The curious thing here is when I retrieve the insert id from insert method it returns the correct value, but the actual database field is null.

如果我理解正确声明为INTEGER PRIMARY KEY的列会自动递增。的 [引用]

推荐答案

从技术文档：

一个表使用CREATE TABLE AS没有PRIMARY KEY创建并没有   限制任何形式的。每列的默认值是NULL

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 的约束。
说明：

You don't have to add UNIQUE constraint on a COLUMN that has PRIMARY KEY constraint.
Explanation:

一个UNIQUE约束类似于PRIMARY KEY约束，除非   单个表可具有任何数量的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 。 这就是为什么：

Instead add NOT NULL. This is why:

根据SQL标准，主键应该始终意味着不   空值。不幸的是，由于一些早期版本的bug，这不是   SQLite中的情况。除非列是一个整数PRIMARY KEY或   表是一个无ROWID表或者列被声明为NOT NULL，   SQLite的允许在主键列NULL值。 SQLite的可能   固定到符合标准，但这样做可能会破坏传统   应用程序。因此，已决定仅仅记录的事实   是的SQLite允许在大多数主键列空值。

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.

我建议使用此列定义：

I recommend using this Column definition:

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

这篇关于SQLite的主键自动增加不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！