![]() |
UNIQUE constraint failed: sqlite database : android - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +--- Thread: UNIQUE constraint failed: sqlite database : android (/Thread-UNIQUE-constraint-failed-sqlite-database-android) Pages:
1
2
|
UNIQUE constraint failed: sqlite database : android - exactly232251 - 07-20-2023 I am trying to insert values in table. But there is only one value inserted. I am getting an error in log cat when I am trying to insert new values. Log cat shows : abort at 13 in [INSERT INTO event(totalminutesfrom,dayofweek,title,location,totalminutesto,id) VALUES (?,?,?,?,?,?)]: UNIQUE constraint failed: event.id 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: Error inserting totalminutesfrom=694 dayofweek=null title=qxs location=Eded & Mariz totalminutesto=0 id=0 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: event.id (code 1555) 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method) 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782) 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788) 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86) 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1471) 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341) 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at com.example.siddhi.timetablelayout.EventTableHelper.addEvent(EventTableHelper.java:76) 01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at com.example.siddhi.timetablelayout.AddEventActivity$5.onClick(AddEventActivity.java:217) Its showing error on these two lines while inserting row. db.insert(TABLE, null, values); db.addEvent(new EventData(eventTitle,dayOfWeek,totalMinutesFrom, totalMinutesTo,location)); EventTableHelper public class EventTableHelper extends SQLiteOpenHelper { private static final String TABLE = "event"; private static final String KEY_ID = "id"; private static final String KEY_TITLE = "title"; private static final String KEY_LOCATION = "location"; private static final String KEY_DAY_OF_WEEK = "dayofweek"; private static final String KEY_TOTAL_MINUTES_FROM = "totalminutesfrom"; private static final String KEY_TOTAL_MINUTES_TO = "totalminutesto"; public EventTableHelper(Context context) { super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION); //3rd argument to be passed is CursorFactory instance } // Creating Tables @Override public void onCreate(SQLiteDatabase db) { //createTable(db); } public void createTable(SQLiteDatabase db){ String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TITLE + " TEXT," + KEY_DAY_OF_WEEK +"TEXT" + KEY_TOTAL_MINUTES_FROM +"INTEGER" + KEY_TOTAL_MINUTES_TO + "INTEGER" + KEY_LOCATION + "TEXT" + ")"; db.execSQL(CREATE_EVENTS_TABLE); } // Upgrading database @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // Drop older table if existed // db.execSQL("DROP TABLE IF EXISTS " + TABLE); // createTable(db); // Create tables again //onCreate(db); } // code to add the new contact public void addEvent(EventData event) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_ID, event.getId()); values.put(KEY_TITLE,event.getTitle()); // Contact Name values.put(KEY_DAY_OF_WEEK,event.getDayofWeek()); values.put(KEY_TOTAL_MINUTES_FROM,event.getFromMinutes()); values.put(KEY_TOTAL_MINUTES_TO,event.getToMinutes()); values.put(KEY_LOCATION,event.getLocation()); // Inserting Row db.insert(TABLE, null, values); //2nd argument is String containing nullColumnHack db.close(); // Closing database connection } // code to get the single contact EventData getEvent(int id) { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.query(TABLE, new String[] { KEY_ID, KEY_TITLE, KEY_DAY_OF_WEEK, KEY_TOTAL_MINUTES_FROM,KEY_TOTAL_MINUTES_TO,KEY_LOCATION }, KEY_ID + "=?", new String[] { String.valueOf(id) }, null, null, null, null); if (cursor != null) cursor.moveToFirst(); EventData eventData = new EventData(Integer.parseInt(cursor.getString(0)),cursor.getString(1), cursor.getString(2), cursor.getInt(3),cursor.getInt(4),cursor.getString(5)); return eventData; } // code to get all contacts in a list view public List<EventData> getAllEvents() { List<EventData> conList = new ArrayList<EventData>(); // Select All Query String selectQuery = "SELECT * FROM " + TABLE; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { do { EventData event = new EventData(); event.setId(Integer.parseInt(cursor.getString(0))); event.setTitle(cursor.getString(1)); event.setDayofWeek(cursor.getString(2)); event.setFromMinutes(cursor.getInt(3)); event.setToMinutes(cursor.getInt(4)); event.setLocation(cursor.getString(5)); // Adding contact to list conList.add(event); } while (cursor.moveToNext()); } // return contact list return conList; } } How to solve this?? RE: UNIQUE constraint failed: sqlite database : android - Mrmarkoc - 07-20-2023 The table has a unique constraint on it. That means that only one row can exist with a given ID value. If you're trying to change some of the values for a row, use UPDATE not INSERT. If you're trying to add this row, you need to either give it a different, unique ID or you need to delete the pre-existing row first. Which of these is the right answer depends on what your app is doing. RE: UNIQUE constraint failed: sqlite database : android - Mrunstationed847 - 07-20-2023 Your code probably violates primary key's uniqueness constraint on a `KEY_ID` field. Two possible solutions are: 1. Make sure that your `EventData.getId()` returns unique values per object. For now, I don't see you pass any identifier to its constructor and perhaps all the events are inserted with the same `id` value. 2. If you don't care about generating ids by yourself, you can add `AUTOINCREMENT` setting to your `KEY_ID` column definition. This way `KEY_ID` field will be filled automatically and each row will have its own, unique value. Once there, don't forget to remove adding `KEY_ID` to `ContentValues` by yourself. RE: UNIQUE constraint failed: sqlite database : android - fycedfotda - 07-20-2023 Try checking if the ID is already existed. If true, do not insert, because you already have the row with this ID. RE: UNIQUE constraint failed: sqlite database : android - gile848 - 07-20-2023 I originally put the new unique constraint infant of the old one. Instead make sure you're current unique column is first: CREATE TABLE TEST (client_id TEXT unique, remote_id INT unique) RE: UNIQUE constraint failed: sqlite database : android - ephorus417488 - 07-20-2023 make id column id integer autoincrement, and do not put the id value into content values. RE: UNIQUE constraint failed: sqlite database : android - gentlefolksmfx - 07-20-2023 For developers using [Room Persistence Library][1]. You can use @Insert(onConflict = OnConflictStrategy.REPLACE) // or OnConflictStrategy.IGNORE in DAO according to [Insert Documentation][2] [1]: [To see links please register here] [2]:[To see links please register here] RE: UNIQUE constraint failed: sqlite database : android - cascadian664711 - 07-20-2023 I had the same problem and the problem was that I forgot to add `db.execSQL(YOUR_TABLE);` in my `DbHelper` RE: UNIQUE constraint failed: sqlite database : android - genesis432 - 07-20-2023 If you use **Room** then instead of `@PrimaryKey` you should use `@PrimaryKey(autoGenerate = true)` and make your id variable optional: `@Entity(tableName = "contact_table") data class Contact(@PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") var id: Long?, @ColumnInfo(name = "name") val name: String, @ColumnInfo(name = "phone") val phone: String)` and then when adding a new item, pass `null` as id, `insert` func will return new id, add it to your object val contact = Contact(null, name, phone) contact.id = ContactRoomDatabase.getDatabase().contactDao().insert(contact) RE: UNIQUE constraint failed: sqlite database : android - faenza977 - 07-20-2023 This is the case of duplicate id of the record in the database. Just clear the app storage and try again. A good solution would be adding below in your Dao class. @Insert (onConflict = OnConflictStrategy.REPLACE) Correct solution to this problem is to make sure that the id is unique. Please have a look at this [Google Codelabs Room Example][1] [1]: [To see links please register here] Another way to avoid this would be adding ` @PrimaryKey(autoGenerate = true) ` |