CODEWITHSUNDEEP
androidsqliteandroid-contentprovider
Jie Li
Jie Li

Reputation: 13

android sqlite database contraint

I have a local database to store the movie information provided by moviedb.org api. Each time the code will bulkinsert these information into my local database, but I got unique constraint error. I know its because I have set the movie id to be unique, since I thought at first I have to avoid duplication of same information.

Here is my database code :

public void onCreate(SQLiteDatabase db) {
    final String SQL_CREATE_MOVIE_TABLE = "CREATE TABLE " + MovieContract.MovieEntry.TABLE_NAME + " (" +
            MovieContract.MovieEntry._ID + " INTEGER PRIMARY KEY," +
            MovieContract.MovieEntry.COLUMN_MOVIE_ID + " INTEGER UNIQUE NOT NULL, " +
            MovieContract.MovieEntry.COLUMN_MOVIE_NAME + " TEXT NOT NULL, " +
            MovieContract.MovieEntry.COLUMN_MOVIE_GENRE + " INTEGER NOT NULL, " +
            MovieContract.MovieEntry.COLUMN_MOVIE_OVERVIEW + " TEXT NOT NULL, " +
            MovieContract.MovieEntry.COLUMN_MOVIE_RELEASE_DATE + " TEXT NOT NULL, " +
            MovieContract.MovieEntry.COLUMN_MOVIE_POSTER_PATH + " TEXT, " +
            MovieContract.MovieEntry.COLUMN_MOVIE_POPULARITY + " DOUBLE NOT NULL, " +
            MovieContract.MovieEntry.COLUMN_MOVIE_RATING + " DOUBLE NOT NULL " +
            " );";

    db.execSQL(SQL_CREATE_MOVIE_TABLE);
}

Here is my bulkinsert function:

public int bulkInsert(Uri uri, ContentValues[] values) {
        final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
        final int match = sUriMatcher.match(uri);
        switch (match) {
            case MOVIE:
                db.beginTransaction();
                int returnCount = 0;
                try {
                    for (ContentValues value : values) {
                        long _id = db.insert(MovieContract.MovieEntry.TABLE_NAME, null, value);
                        if (_id != -1) {
                            returnCount++;
                        }
                    }
                    db.setTransactionSuccessful();
                } finally {
                    db.endTransaction();
                }
                getContext().getContentResolver().notifyChange(uri, null);
                return returnCount;
            default:
                return super.bulkInsert(uri, values);
        }
    }

So, I'd like to know if I remove this unique keyword, will bulkinsert still insert the duplication information or it will update automatically the information of same id? If not, what should i do to ensure that bulkinsert do nothing if it's same information, update if id already exist but other relative information changed. Thanks.

Upvotes: 1

Views: 39

Answers (1)

e4c5
e4c5

Reputation: 53734

If you simply remove the UNIQUE constraint and attempt to bulk insert data, if the same item is repeated it will be duplicated in your database. However you can replace your insert method call with a call to insertWithOnConflict

You can then decide how exactly duplicates should be handled. The default is to ignore (CONFLICT_IGNORE). You are probably looking for CONFLICT_REPLACE

When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row. Thus the insert or update always occurs. The command continues executing normally.

But either CONFLICT_IGNORE or CONFLICT_REPLACE can be used depending on your requirement.

Upvotes: 1

Related Questions