CODEWITHSUNDEEP
androidsqlite
AnthonyW
AnthonyW

Reputation: 1998

NOT NULL columns in SQLite and error catching

I have a database that is being filled by user defined EditTexts. None of the edit texts should allow empty fields. I know that I can check for this with a couple simple if-statements: if myEditText.getText().toString().equals("") // display error. However I would perfer to use this opportunity to brush up on my SQLite and error catching (as demonstrated in my add method). How would I go about altering the columns in the table below to NOT NULL and generating/catching an error when a user attempts to add/update with empty fields?

My database table:

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

My add method:

... fill ContentValues values

try{
db.getWritableDatabase().insertWithOnConflict(DatabaseHelper.TABLE_NAME, DatabaseHelper.CATEGORY, values, SQLiteDatabase.CONFLICT_FAIL);
fillItemNumbers();          
}
catch(SQLiteConstraintException e)
{
Toast
.makeText(MyActivity.this, etItemNum.getText().toString() + " already exists in " +     catSpinner.getSelectedItem().toString() +". Consider using Update.",Toast.LENGTH_LONG)
.show();            
}

My update method:

... fill ContentValues values

String[] args = {catSpinner.getSelectedItem().toString(), etItemNum.getText().toString()};
int rowsAffected = db.getWritableDatabase().update(DatabaseHelper.TABLE_NAME, values, DatabaseHelper.CATEGORY + "=? AND " + DatabaseHelper.ITEM_NUMBER + "=?" , args);

UPDATE: I did a little digging and came up with this:

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

Is this what I am looking for? If so, how can I use this to my advantage (see above)?

Upvotes: 2

Views: 9609

Answers (2)

Barak
Barak

Reputation: 16393

All you need to do is set the columns to NOT NULL.

Then use

insertWithOnConflict(String table, String nullColumnHack, ContentValues initialValues, int conflictAlgorithm)

and 

updateWithOnConflict(String table, ContentValues values, String whereClause, String[] whereArgs, int conflictAlgorithm)`

There are several constants you can use for the conflictAlgorithm, depending on exactly what you want to happen. If you want to simply not enter the data into the table, CONFLICT_IGNORE will do the trick. If you want a return code letting you know so you can act on it (let the user know) then you might want CONFLICT_FAIL.

See this for further information.

Hope this helps.

Upvotes: 1

Mayank
Mayank

Reputation: 8852

I am not sure if you can actually Alter Column Definition for table. I know you can Alter Table itself, like adding new Column to Table. You might need little trick to modify your database if there is lot of data in it that you want to preserve.

One way to it to create new table and try copying data to new table and afterwards remove old table and rename new Table. It's not most efficient way to do it but it'll get the job done though.

http://www.sqlite.org/lang_altertable.html

1

EDIT

Here you go

CREATE TABLE inventory (category TEXT not null, itemNum TEXT not null, quantity INTEGER not null, price REAL not null, image INTEGER not null, UNIQUE(category, itemNum) ON CONFLICT FAIL);

EDIT 2 Try this

CREATE TABLE inventory (category TEXT not null ON CONFLICT FAIL, itemNum TEXT not null ON CONFLICT FAIL, quantity INTEGER not null ON CONFLICT FAIL, price REAL not null ON CONFLICT FAIL, image INTEGER not null ON CONFLICT FAIL, UNIQUE(category, itemNum) ON CONFLICT FAIL);

Upvotes: 3

Related Questions