SQLite creates table with unique combination of columns

I went through several sites for any useful documentation and came out empty. The flowcharts from the official site can be Greek as well, and the examples I tried to draw from other relevant posts on that site got me wrong. I am writing a simple application to host user input in a database, but I need a combination of 2 columns to be unique . From what I've seen, this can be achieved with UNIQUE or PRIMARY KEY. I also need some way to capture the error to tell the user that their input is faulty . I know that I can do this on the Java side easily enough, but I would rather not iterate over the table in every insert suggested.

This is what I have so far:

db.execSQL("CREATE TABLE inventory (category TEXT, itemNum TEXT, quantity INTEGER, price REAL, image INTEGER, UNIQUE(category, itemNum) ON CONFLICT FAIL;");

      

The table was built correctly until I added UNIQUE ...

What threw: ERROR / SQLiteOpenHelper (1037): android.database.sqlite.SQLiteException: next to ";": syntax error: inventory CREATE TABLE (TEXT category, itemNum TEXT, INTEGER count, REAL price, INTEGER image, CONSTRAINT unq UNIQUE ( category, itemNum) CONFLICT MALFUNCTION;

EDIT:

... populate ContentValues ​​with user input.

try{
db.getWritableDatabase().insert(DatabaseHelper.TABLE_NAME, DatabaseHelper.CATEGORY, values);
fillItemNumbers();  // Updates screen       
}
catch(SQLiteConstraintException e)
{
Toast
.makeText(this, "User error",Toast.LENGTH_LONG)
.show();            
}

      

+3


source to share


1 answer


You have an inconsistent parenthesis. After that there FAIL

should be something else )

.

Once you've sorted it, you can catch the exception that happens when the constraint is violated.



try {
    // insert new data
} catch (SQLiteConstraintException e) {
    Toast.makeText(context, 
                   "The combination of A and B must be unique",
                   Toast.LENGTH_LONG).show();
}

      

+2


source







All Articles