1555 android data_error ai_generated true

android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: users.email (code 1555)

ID: android/sqliteconstraintexception-unique-constraint

Also available as: JSON · Markdown · 中文
90%Fix Rate
87%Confidence
1Evidence
2023-04-20First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
SQLite 3.42 active — — —
Android 12 (API 31) active — — —
Android 13 (API 33) active — — —
Room 2.5.2 active — — —

Root Cause

An INSERT or UPDATE operation attempted to set a value on a column with a UNIQUE constraint that already exists in the table.

generic

中文

INSERT 或 UPDATE 操作尝试在具有 UNIQUE 约束的列上设置一个表中已存在的值。

Official Documentation

https://developer.android.com/reference/android/database/sqlite/SQLiteConstraintException

Workarounds

  1. 90% success Use INSERT OR REPLACE or INSERT OR IGNORE in your SQL statement. Example: ContentValues values = new ContentValues(); values.put("email", "[email protected]"); db.insertWithOnConflict("users", null, values, SQLiteDatabase.CONFLICT_REPLACE); 
    Use INSERT OR REPLACE or INSERT OR IGNORE in your SQL statement. Example: ContentValues values = new ContentValues(); values.put("email", "[email protected]"); db.insertWithOnConflict("users", null, values, SQLiteDatabase.CONFLICT_REPLACE);
  2. 85% success Check for existing data before inserting: if (db.rawQuery("SELECT 1 FROM users WHERE email = ?", new String[]{email}).getCount() == 0) { db.insert(...); } 
    Check for existing data before inserting: if (db.rawQuery("SELECT 1 FROM users WHERE email = ?", new String[]{email}).getCount() == 0) { db.insert(...); }
  3. 95% success Use Room's @Insert(onConflict = OnConflictStrategy.REPLACE) annotation to handle duplicate entries automatically. 
    Use Room's @Insert(onConflict = OnConflictStrategy.REPLACE) annotation to handle duplicate entries automatically.

中文步骤

  1. Use INSERT OR REPLACE or INSERT OR IGNORE in your SQL statement. Example: ContentValues values = new ContentValues(); values.put("email", "[email protected]"); db.insertWithOnConflict("users", null, values, SQLiteDatabase.CONFLICT_REPLACE);
  2. Check for existing data before inserting: if (db.rawQuery("SELECT 1 FROM users WHERE email = ?", new String[]{email}).getCount() == 0) { db.insert(...); }
  3. Use Room's @Insert(onConflict = OnConflictStrategy.REPLACE) annotation to handle duplicate entries automatically.

Dead Ends

Common approaches that don't work:

  1. 95% fail

    Clearing app cache does not affect the database, so the error persists.

  2. 75% fail

    Adding a try-catch around the INSERT without handling the duplicate (e.g., ignoring the error) may cause data loss or silent failures.

  3. 85% fail

    Disabling the unique constraint via ALTER TABLE may break data integrity and cause other bugs.