Failure to delete from the Sqlite database table (Regarding unusual behavior)

Question

I have a strange problem in my Android app related to database relationships by foreign keys. The following codes describe my simple database structure, I used SQLiteOpenHelper as a superclass to work with the database

private static final String CATEGORIES_TABLE = "CREATE TABLE __CATEGORIES_TBL(_id INTEGER PRIMARY KEY AUTOINCREMENT, _name TEXT NOT NULL, _desc TEXT NULL," + "_create_date INTEGER NOT NULL, _update_date INTEGER NULL" + ", _parent_id INTEGER NULL, FOREIGN KEY(_parent_id) REFERENCES __CATEGORIES_TBL(_id) ON DELETE RESTRICT);"; private static final String CARDS_TABLE = "CREATE TABLE __CARDS_TBL(_id INTEGER PRIMARY KEY AUTOINCREMENT," + "_value TEXT NOT NULL, _play_count INTEGER NULL, " + "_category_id INTEGER NOT NULL, FOREIGN KEY(_category_id) REFERENCES __CATEGORIES_TBL(_id) ON DELETE RESTRICT);"; @Override public void onCreate(SQLiteDatabase db) { try { db.execSQL("PRAGMA foreign_keys = ON;"); db.execSQL(CATEGORIES_TABLE); db.execSQL(CARDS_TABLE); Logger.i("DB-INIT-DONE!"); } catch (Exception ex) { Logger.e("Database on create error", ex); } } 

as you see, everything seems OK, and it is; I can insert, edit, select rows in / from both tables, but unfortunately I can delete rows that have child rows. as I expected. because I set the FK relation (foreign key) between the tow tables with ON DELETE RESTRICT mode, so I expect to get an exception when I try to delete a row from the parent table (__CATEGORIES_TBL), in fact the parent record is deleted and the exception happens,

sqlite theory needs to prevent the removal of any row in __CATEGORIES_TBL when it has one or more child rows in __CARDS_TBL or any child rows in __CATEGORIES_TBL, but in my application I can delete rows when it has a parent relationship row,

consider the following code (this is the delete code)

 private SQLiteDatabase db; public long delete(long objId) { try { // TABLE_NAME can be __CATEGORIES_TBL or __CARDS_TBL based on program flow return db.delete(TABLE_NAME, COLUMN_ROWID + "=" + objId, null); } catch (Exception e) { Logger.d("Unable to delete category <" + objId + ">.", e); return -123456; } } 

each db.delete call returns 1 (means that this line has been deleted by this command), this code is executed in android 2.3;

Thanks in advance.

Answer 1

I get the behavior that I expect from SQLite 3.7.9.

 sqlite> CREATE TABLE __CATEGORIES_TBL ( ...> _id INTEGER PRIMARY KEY AUTOINCREMENT, ...> _name TEXT NOT NULL, ...> _desc TEXT NULL, ...> _create_date INTEGER NOT NULL, ...> _update_date INTEGER NULL, ...> _parent_id INTEGER NULL, ...> FOREIGN KEY(_parent_id) ...> REFERENCES __CATEGORIES_TBL(_id) ON DELETE RESTRICT ...> ); sqlite> sqlite> CREATE TABLE __CARDS_TBL( ...> _id INTEGER PRIMARY KEY AUTOINCREMENT, ...> _value TEXT NOT NULL, ...> _play_count INTEGER NULL, ...> _category_id INTEGER NOT NULL, ...> FOREIGN KEY(_category_id) ...> REFERENCES __CATEGORIES_TBL(_id) ON DELETE RESTRICT ...> ); sqlite> sqlite> insert into __categories_tbl values ...> (1, 'name', 'desc',current_date,current_date,1); sqlite> insert into __cards_tbl values (1, 'value',3, 1); sqlite> pragma foreign_keys=on; sqlite> select * from __categories_tbl; 1|name|desc|2012-08-25|2012-08-25|1 sqlite> delete from __categories_tbl; Error: foreign key constraint failed 

If I were you, I would try to visually check the SQLite database after each step to see if what you expect is really happening. IIRC, not every failure raises an exception.

---

Foreign key support was introduced in version 3.6.19. There are compile-time options that allow SQLite to analyze foreign key constraints, but prevent them from actually applying them. (SQLITE_OMIT_TRIGGER is defined, SQLITE_OMIT_FOREIGN_KEY is not defined.) You need to know if your SQLite assembly can force foreign keys when trying to create a trigger.

If the trigger creation fails with a parsing error, SQLite will parse the external statements, but will not apply them. You will need to recompile your assembly 3.6.19 or upgrade to a newer version. (And check these settings before compiling.)

A column declared NOT NULL may appear empty if you insert an empty row. By default, NULL and blank lines look the same in output. You can indicate whether the column contains zeros by selecting them.

 select * from table_name where column_name is null; 

Answer 2

About this problem I set db.execSQL ("pragma foreign_keys = on;"); before deleting in the application code, then the problem is solved, but I think this is unnecessary code, maybe my phone installed by sqlite configs is wrong and needs to be reconfigured, is it possible to reconfigure sqlite on the phone? the test device is HTC Wildfire s (version of SQL version is 3.7.2)