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();
}
source to share
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();
}
source to share