Invalid sqlite error code

Question

I have two albums and songs, and each song has a link to alum id.

Album table:

CREATE TABLE albums (id INTEGER PRIMARY KEY ASC, name TEXT, additional TEXT) 

Song table:

 CREATE TABLE songs (id INTEGER PRIMARY KEY ASC, album_fk INTEGER NOT NULL, title TEXT, url TEXT, duration BIGINT NOT NULL) 

and I also have a trigger to check when I delete the album, if it has existing songs in it:

 CREATE TRIGGER trigger_on_delete BEFORE DELETE ON albums FOR EACH ROW BEGIN SELECT RAISE(FAIL,'album has songs cannot be deleted') WHERE (SELECT album_fk FROM songs WHERE album_fk = OLD.id) IS NOT NULL;) END 

it all works great, but sometimes

 Exception: android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed Stack Trace : android.database.sqlite.SQLiteStatement.native_execute(Native Method) android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java: 66) 

throws when I try to delete an album. My delete method simply deletes the album by id. The bad thing is that I can’t reproduce this, so I can’t provide a lot of information. I tried different scenarios with deleting the album, but I did not get this exception.

So, any ideas how you can research this problem. There are no restrictions on the album table, so what might cause such an exception? Any help would be appreciated.

Answer 1

This is so unfair. If you run this code on a PC, everything will be all right, and if you try to delete an album that has songs, the exception says: the album cannot be deleted ", but it seems that androids are not processing the correct RAISE from sqlite and jsut throw. Exception: android.database.sqlite.SQLiteConstraintException: error code 19: restriction not met.

Answer 2

I solved this by correcting my database definition in a field that is not NULL. You must send some data.

Answer 3

I had the same problem with some triggers in backgroung, I finally switched to this classic option and it fixed my problem:

db.rawQuery ("UPDATE table set column = newValue WHERE ...;", null);

Answer 4

See also "SQLiteStatement does not propagate error messages defined in SQLite triggers" http://code.google.com/p/android/issues/detail?id=3296