sqlite constraint failed error code

Question

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.

First of all, I would recommend you to download SQLite database browser. This software offers the possibility to test your queries after exporting them from the emulator. This way you can check if there is any error. Moreover, just after you raise function, you have a ; but it seems to cut your query in the middl because it is followed by a where — Sephy
– Sephy, Commented Jul 30, 2010 at 12:42
The first time you created your table , was it without any constraints as it is in your post? If you run for the first time with constraints, the table will be created. Then if you remove the constraint and re-run your project, the table will not be recreated (if you just use getWrtiableDatabase()) and you will have the old version (with constraint). So you will have to delete it first. — ccheneson
– ccheneson, Commented Jul 30, 2010 at 14:18

Mojo Risin · Accepted Answer · 2010-07-30 13:41:25Z

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.

answered Jul 30, 2010 at 13:41

Mojo Risin

8,1425 gold badges48 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

blahman · Accepted Answer · 2012-01-30 04:44:13Z

2

I solved this by correcting the definition of my database to a field that is NOT NULL. You must send some data.

edited Jan 30, 2012 at 4:44

blahman

1,3141 gold badge13 silver badges19 bronze badges

answered Jan 30, 2012 at 3:36

julian velazquez

212 bronze badges

Comments

Youyougoslave · Accepted Answer · 2011-06-24 02:29:00Z

1

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

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

answered Jun 24, 2011 at 2:29

Youyougoslave

5516 silver badges8 bronze badges

Comments

MikeW · Accepted Answer · 2011-04-12 12:39:14Z

0

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

answered Apr 12, 2011 at 12:39

MikeW

412 bronze badges

Collectives™ on Stack Overflow

sqlite constraint failed error code

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related