UNIQUE constraint failed: type.ps

Question

My dictionary app project uses SQLite for storing data. I'm getting UNIQUE constraint failed: type.ps in the methodcreateTablesAndProcess() when I run the app.

createTablesAndPorcess method:

public final void createTablesAndProcess() {
    boolean z = true;
    try {
        if (!doesExistsTable("other_words")) {
            this.wdb.execSQL("create table if not exists other_words(word TEXT PRIMARY KEY)");
            this.wdb.execSQL("INSERT INTO other_words ( word ) SELECT DISTINCT w FROM ( SELECT ot AS w FROM oten UNION SELECT word AS w FROM other )");
        }
    } catch (Exception e) {
        try {
            e.printStackTrace();
            z = false;
        } catch (Exception e2) {
            e2.printStackTrace();
            return;
        }
    }
    try {
        if (!doesExistsTable("new_study_plan")) {
            this.wdb.execSQL("create table if not exists new_study_plan(serial INTEGER PRIMARY KEY , word TEXT , add_time INTEGER NOT NULL)");
        }
    } catch (Exception unused) {
    }
    try {
        if (!doesExistsTable("new_study_plan_other")) {
            this.wdb.execSQL("create table if not exists new_study_plan_other(serial INTEGER PRIMARY KEY , word TEXT , add_time INTEGER NOT NULL)");
        }
    } catch (Exception unused2) {
    }
    try {
        if (!doesExistsTable("new_added")) {
            this.wdb.execSQL("create table if not exists new_added(word TEXT PRIMARY KEY , details TEXT  NOT NULL)");
        }
    } catch (Exception unused3) {
    }
    try {
        if (!doesExistsTable("new_history")) {
            this.wdb.execSQL("create table if not exists new_history(serial INTEGER PRIMARY KEY , word TEXT , add_time INTEGER NOT NULL)");
        }
    } catch (Exception unused4) {
    }
    if (!doesExistsTable("new_history_other")) {
        this.wdb.execSQL("create table if not exists new_history_other(serial INTEGER PRIMARY KEY , word TEXT , add_time INTEGER NOT NULL)");
    }

//Crash on the below line.
    this.wdb.execSQL("INSERT INTO type VALUES ( NULL,'MORE')");
    if (z) {
        manageInThread();
    }
}

initializeDatabase task:

private void initilizeDatabase(final int i, final boolean z) {
    new AsyncTask<String, String, String>() {
        protected void onPreExecute() {
            try {
                if (i == Constants.DB_VERSION) {
                    DictionaryActivity.this.progressMessageView.setVisibility(8);
                } else {
                    DictionaryActivity.this.progressMessageView.setVisibility(0);
                }
            } catch (Exception e) {
                LogUtils.log(DictionaryActivity.this, e);
            }
        }

        protected String doInBackground(String... strArr) {
            if (z) {
                DatabaseHelper.backupInFile(DictionaryActivity.this, i);
            }
            DatabaseHelper.manageDatabase(DictionaryActivity.this, false);
            try {
                DictionaryActivity.this.databaseAccessor = new DatabaseAccessor(DictionaryActivity.this);
            } catch (Exception e) {
                e.printStackTrace();
            }
            DictionaryActivity.this.databaseAccessor.createTablesAndPorcess();

     //Crash while executing above line.
            DictionaryActivity.this.databaseAccessor.initTypeMapping(DictionaryActivity.this.IDSToPS, DictionaryActivity.this.PSToIDS, DictionaryActivity.this.PS_VALUES, DictionaryActivity.this.PS_IDS);
            if (z) {
                DictionaryActivity.this.databaseAccessor.loadWordFromFile(DictionaryActivity.this);
                DictionaryActivity.this.databaseAccessor.restoreFile(DictionaryActivity.this, DictionaryActivity.this.getDatabasePath(Constants.BACKUP_FILE_NAME));
            }
            return null;
        }

        protected void onPostExecute(String str) {
            try {
                DictionaryActivity.this.mainViewAnimator.setDisplayedChild(1);
                DictionaryActivity.this.addTabs(DictionaryActivity.TITLE_IDS);
                if (DictionaryActivity.this.sharedPreferences.getInt(Constants.KEY_LAST_SHOWN_POPUP_MESSAGE_ID, 0) != 5) {
                    DictionaryActivity.this.showInitialMessage(false);
                } else {
                    DictionaryActivity.this.manageAutoPopups();
                }
                DictionaryActivity.this.showSharedText(DictionaryActivity.this.getIntent());
                Utils.createShortCut(DictionaryActivity.this, DictionaryActivity.class);
                Utils.manageClipboardAndNotificationbar(DictionaryActivity.this);
                try {
                    DictionaryActivity.this.isTTSRequested = false;
                    Intent intent = new Intent();
                    intent.setAction("android.speech.tts.engine.CHECK_TTS_DATA");
                    DictionaryActivity.this.startActivityForResult(intent, DictionaryActivity.CHECK_TTS);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                DictionaryActivity.this.showHideOCR(true);
                DictionaryActivity.this.showOverlayPermissionPopupIfRequired();
            } catch (Exception e2) {
                if (!(e2 instanceof ActivityNotFoundException)) {
                    LogUtils.log(DictionaryActivity.this, e2);
                }
            }
            DictionaryActivity.this.isExitingOk = true;
            if (DictionaryActivity.this.IDSToPS.size() == 0) {
                DictionaryActivity.this.resetDatabase();
            }
        }
    }.execute();
}

The error is coming when I call createTablesAndProcess in the initialize method and at this.wdb.execSQL("INSERT INTO type VALUES ( NULL,'MORE')"); line. Let me know in the comments if you want more logs.

Update - Logcat:

2019-06-19 13:31:56.289 20450-20479/com.hdictionary.gu E/AndroidRuntime: FATAL EXCEPTION: AsyncTask #1
Process: com.hdictionary.gu, PID: 20450
java.lang.RuntimeException: An error occurred while executing doInBackground()
    at android.os.AsyncTask$3.done(AsyncTask.java:318)
    at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:354)
    at java.util.concurrent.FutureTask.setException(FutureTask.java:223)
    at java.util.concurrent.FutureTask.run(FutureTask.java:242)
    at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:243)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1133)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:607)
    at java.lang.Thread.run(Thread.java:760)
 Caused by: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: type.ps (code 2067)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:734)
    at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
    at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1677)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1606)
    at com.bappi.db.DatabaseAccessor.createTablesAndPorcess(DatabaseAccessor.java:152)
    at com.hdictionary.gu.DictionaryActivity$10.doInBackground(DictionaryActivity.java:1130)
    at com.hdictionary.gu.DictionaryActivity$10.doInBackground(DictionaryActivity.java:1100)
    at android.os.AsyncTask$2.call(AsyncTask.java:304)

Please provide `SHOW CREATE TABLE type` and explain the purpose of the table. — Rick James, Jun 20 '19 at 13:27

score 0 · Answer 1 · answered Jun 22 '19 at 12:30

It is a simple error. Each element of column ps in the table type should be unique since you created the table that way. But at the time of executing the statement INSERT INTO type VALUES ( NULL,'MORE') It somehow have the same value (if it is the first column, then a NULL) in the column ps already.

UNIQUE constraint failed: type.ps

1 Answers1