CODEWITHSUNDEEP
androidsqlitecrash
Omar
Omar

Reputation: 8145

Crash in SQLite Insert "Column are not unique"

I'm trying to avoid duplicate data on my Database, so I made UNIQUE columns, and I can normally insert to them.

The problem is when I keep inserting the same thing more than 1 time, then the app crashes.. Here is logcat:

07-03 10:33:53.577: ERROR/Database(19974): Failure 19 (columns SuraNumber, AyaNumber are not unique) on 0x2dc560 when executing 'INSERT INTO Favorite_Sura (SuraNumber,AyaNumber) VALUES (17,1)'
07-03 10:33:54.440: ERROR/AndroidRuntime(19974): FATAL EXCEPTION: Thread-11
07-03 10:33:54.440: ERROR/AndroidRuntime(19974): android.database.sqlite.SQLiteConstraintException: columns SuraNumber, AyaNumber are not unique: INSERT INTO Favorite_Sura (SuraNumber,AyaNumber) VALUES (17,1)
07-03 10:33:54.440: ERROR/AndroidRuntime(19974):     at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method)
07-03 10:33:54.440: ERROR/AndroidRuntime(19974):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1763)
07-03 10:33:54.440: ERROR/AndroidRuntime(19974):     at omar.quran1.Quran$16.run(Quran.java:910)
07-03 10:33:54.440: ERROR/AndroidRuntime(19974):     at java.lang.Thread.run(Thread.java:1019)

The code I use to Insert, both SuraNumber and CurrentAya are UNIQUE columns:

     new Thread(new Runnable() {
                    public void run() {

            /////////////// DATABASE
            File dbfile = new File(Munawwat_Database_PATH); 
            if(!dbfile.exists())
            {
                Toast.makeText(mContext, "error in db location", Toast.LENGTH_SHORT);
            }
            GetStartPageAya();
            SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbfile, null);
            db.execSQL("INSERT INTO Favorite_Sura (SuraNumber,AyaNumber) VALUES ("+SuraNumber+","+CurrentAya+")");
            db.close();
                    }}).start();

Why is this happening? Thanks.

UPDATE1: The sql I used to create the table:

CREATE TABLE Favorite_Sura
(
_Id INTEGER PRIMARY KEY,
SuraNumber int,
AyaNumber int,
UNIQUE (SuraNumber, AyaNumber)
)

Upvotes: 0

Views: 8037

Answers (2)

Philip Sheard
Philip Sheard

Reputation: 5825

You have the wrong design for your table. You should define a unique compound index on the pair (sura, ayah).

Upvotes: 0

geekosaur
geekosaur

Reputation: 61457

You are deliberately violating the UNIQUE constraint? I'm not sure what you think should be happening. Catch the obviously-named SQLiteConstraintException if your application can afford to ignore duplicates; usually, a UNIQUE field is unique for a reason, and raising an exception for a duplicate is necessary to maintain application logical consistency.

Upvotes: 3

Related Questions