Android Room 唯一约束失效问题解析与正确实践

import androidx.room.ColumnInfo;
import androidx.room.Entity;
import androidx.room.Index;
import androidx.room.PrimaryKey;
import org.jetbrains.annotations.NotNull;

@Entity(indices = {@Index(value = {"id_from_client"}, unique = true)})
public class Activity {
    @PrimaryKey(autoGenerate = true)
    public int id;

    @NotNull
    @ColumnInfo(name = "id_from_client")
    public String id_from_client;

    // 其他字段和方法...
}

// 错误示例：列名被反引号包裹
@Entity(indices = {@Index(value = {"`id_from_client`"}, unique = true)})
public class Activity {
    // ...
}

// 正确示例：移除反引号
@Entity(indices = {@Index(value = {"id_from_client"}, unique = true)})
public class Activity {
    @PrimaryKey(autoGenerate = true)
    public int id;

    @NotNull
    @ColumnInfo(name = "id_from_client")
    public String id_from_client;

    // ...
}

dependencies {
    // ...
    implementation 'androidx.room:room-runtime:2.4.3' // 或更高版本
    annotationProcessor 'androidx.room:room-compiler:2.4.3' // 确保版本匹配
    // ...
}

@Override
public void createAllTables(SupportSQLiteDatabase _db) {
    _db.execSQL("CREATE TABLE IF NOT EXISTS `Activity` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `id_from_client` TEXT NOT NULL)");
    _db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS `index_Activity_id_from_client` ON `Activity` (`id_from_client`)"); // 正确的唯一索引
    // ...
}

// TheDatabase.java
import android.content.Context;
import androidx.room.Database;
import androidx.room.Room;
import androidx.room.RoomDatabase;

@Database(entities = {Activity.class}, version = 1, exportSchema = false)
public abstract class TheDatabase extends RoomDatabase {
    public abstract ActivityDAO getActivityDAO();

    private static volatile TheDatabase INSTANCE;

    public static TheDatabase getInstance(Context context) {
        if (INSTANCE == null) {
            synchronized (TheDatabase.class) {
                if (INSTANCE == null) {
                    INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                                    TheDatabase.class, "the_database")
                            .allowMainThreadQueries() // 仅为演示方便，生产环境请勿在主线程执行数据库操作
                            .build();
                }
            }
        }
        return INSTANCE;
    }
}

// ActivityDAO.java
import androidx.room.Dao;
import androidx.room.Insert;

@Dao
public interface ActivityDAO {
    @Insert
    void insert(Activity activity);
}

// MainActivity.java
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.os.Bundle;
import androidx.appcompat.app.AppCompatActivity;
import androidx.sqlite.db.SupportSQLiteDatabase;

public class MainActivity extends AppCompatActivity {

    private TheDatabase db;
    private ActivityDAO dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.getActivityDAO();

        // 第一次插入：id_from_client = "100"
        Activity a1 = new Activity();
        a1.id_from_client = "100";
        dao.insert(a1);
        System.out.println("Inserted first activity with id_from_client: 100");

        // 第二次插入：id_from_client = "200"
        Activity a2 = new Activity();
        a2.id_from_client = "200";
        dao.insert(a2);
        System.out.println("Inserted second activity with id_from_client: 200");

        // 打印数据库schema，验证索引是否存在
        SupportSQLiteDatabase sdb = db.getOpenHelper().getWritableDatabase();
        Cursor csr = sdb.query("SELECT * FROM sqlite_master");
        System.out.println("--- Database Schema ---");
        DatabaseUtils.dumpCursor(csr);
        csr.close();
        System.out.println("-----------------------");

        // 第三次插入：尝试插入重复的id_from_client = "200"
        Activity a3 = new Activity();
        a3.id_from_client = "200"; // 重复值
        try {
            dao.insert(a3);
            System.out.println("Unexpected: Inserted third activity with id_from_client: 200 (should fail)");
        } catch (android.database.sqlite.SQLiteConstraintException e) {
            System.err.println("Successfully caught SQLiteConstraintException: " + e.getMessage());
            System.out.println("Expected: Failed to insert activity with duplicate id_from_client: 200");
        } finally {
            // 再次打印数据库schema（通常不会有变化，但可以确认没有异常行为）
            csr = sdb.query("SELECT * FROM sqlite_master");
            System.out.println("--- Database Schema After Third Insert Attempt ---");
            DatabaseUtils.dumpCursor(csr);
            csr.close();
            System.out.println("--------------------------------------------------");
        }
    }
}

I/System.out: Inserted first activity with id_from_client: 100
I/System.out: Inserted second activity with id_from_client: 200
I/System.out: --- Database Schema ---
// ... (此处会打印sqlite_master内容，其中应包含 `CREATE UNIQUE INDEX `index_Activity_id_from_client` ON `Activity` (`id_from_client`)` )
I/System.out: -----------------------
E/System.err: Successfully caught SQLiteConstraintException: UNIQUE constraint failed: Activity.id_from_client (code 2067 SQLITE_CONSTRAINT_UNIQUE)
I/System.out: Expected: Failed to insert activity with duplicate id_from_client: 200
I/System.out: --- Database Schema After Third Insert Attempt ---
// ... (此处再次打印sqlite_master内容，与之前相同)
I/System.out: --------------------------------------------------