Android应用中SQLite的insertWithOnConflict方法失效问题求助

Hey there! Let's break down why your duplicate-ignore insert isn't working and how to fix it.

The Root Cause #

SQLite's conflict handling (like CONFLICT_IGNORE) only kicks in when there's a primary key or unique constraint violation. Right now, your table doesn't have a unique constraint on the field you're using to define "duplicates" (looks like pattern_name), so the database has no way to detect duplicates—hence it just inserts every row regardless.

Step 1: Add a Unique Constraint to Your Table #

First, make sure your table is created with a UNIQUE constraint on the pattern_name column. If you're creating the table for the first time, use a statement like this:

CREATE TABLE " + TABLE_NAME + " (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    pattern_name TEXT UNIQUE, -- This enforces uniqueness for duplicate checks
    value TEXT
);

If your table already exists and has data:

1. First back up any existing data to avoid loss
2. Use this ALTER TABLE statement to add the unique constraint (note: this will fail if there are already duplicate pattern_name entries—clean those up first):

   ALTER TABLE " + TABLE_NAME + " ADD CONSTRAINT unique_pattern_name UNIQUE (pattern_name);
   

Step 2: Clean Up Your Insert Code #

Looking at your code, the second parameter in insertWithOnConflict ("pattern_name") is only used when you're inserting an empty ContentValues (to set a NULL value for that column). Since you're passing valid values, you can safely set this to null:

public void AddPattern(Pattern_Model pattern_model) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put("pattern_name", pattern_model.getPattern_name());
    values.put("value", pattern_model.getPattern_value());
    
    // Use null for the second parameter (it's irrelevant here)
    long ln = db.insertWithOnConflict(TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_IGNORE);
    
    db.close();
    if(ln != -1){
        Log.d("replace", "ok");
    }
}

How to Verify #

After adding the unique constraint, when you try to insert a row with a pattern_name that already exists:

• The ln return value will be -1
• The duplicate row won't be inserted (exactly what you want!)

内容的提问来源于stack exchange，提问作者Priyanka Prajapati