How to achieve an Inheritance relationship in an SQLite database on Android

Question

Consider this simple model:

Location Database:

+-------------------------------+ | Locations | +-------------------------------+ |(PK) _id Integer Autoincrement | | name Text(100) Not null | | is_in_range Integer | +-------------------------------+ 

And a more specialized table called WifiLocation:

 +-------------------------------+ | wifi_location | +-------------------------------+ | ssid Text(0) Not null | | signal_threshold Real | |(PK) _id Integer | +-------------------------------+ 

I want this model to represent WifiLocation inheriting from BaseLocation . So I added the REFERENCES in the _id column of the _id table as follows:

 CREATE TABLE wifi_locations (_id Integer primary key references Locations(_id), ....) 

I am trying to achieve a 1: 1 relationship between these tables.

When I want to insert a row into the wifi_locations table, first insert the corresponding values ​​(Name, IsInRange) into the Locations table and return rowId . Then I insert the rest of the data (ssid) into the wifi_locations table along with rowId as a foreign key.

So, inserting into the Location table works, and I return the identifier, but when I try to use this identifier and insert it into the wifi_locations table, I get an SQL Constraint violation error. There is no more information on what exactly went wrong.

Is there something wrong with my circuit? Is there a better way to achieve this simulation?

EDIT:

Exact error:

 06-16 15:56:42.846: E/Database(2038): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed 

Answer 1

You must create the FOREIGN KEY in the second table, referencing the PRIMARY KEY table in the Tables. In SQLite FOREIGN KEYS supported, but not implicitly included, so you must enable them first.

 final String ENABLE_FOREIGN_KEYS ="PRAGMA foreign_keys=ON"; db.execSQL(ENABLE_FOREIGN_KEYS); 

in the onOpen() method of SQLiteOpenHelper .

Then your FOREIGN KEY looks like this:

 CREATE TABLE wifi_location ( SSID TEXT NOT NULL, SIGNAL_THRESHOLD REAL, _id INTEGER, FOREIGN KEY(_id) REFERENCES Locations(_id) ); 

So, but it all works with SQLite version 3.6.19 . For more information, see SQLite Foreign Key Support .