sqlite constraint failed error code

Question

Hi all, I have two tables albums and songs and each song has reference to alum's 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 an album if there are 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

all this is working fine 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)

is thrown when i try to delete an album. My delete method is simply deleting an album by id.The bad thing is that i can't reproduce this so there is no much info i can provide. I tried different scenarios with deleting an album but i never got this exception.

So any ideas how can investigate this issue. There are not any contraints in album table so what could cause such exception ? Any help will be appreciated.

Answer 1

you wrote 'IN NOT NULL' which should be "IS NOT NULL" I think.

Answer 2

This is so not fair. If you run this code on PC everything is OK and if you try to delete album that has songs the exception says :album has songs cannot be deleted" but it seems that android guys don't handle correct RAISE from sqlite and jsut throw Exception: android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed.