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

Question

Consider this simple model:

A base Location table:

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

And more specialized table called WifiLocation:

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

I want that this model will represents a WifiLocation inheriting from BaseLocation. So I added a REFERENCES clause on the _id column of the wifi_locations table like that:

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

I'm trying to achieve a 1:1 relationship between those tables.

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

So the insertion to the Location table is working, and I'm getting back an Id, but when I try to use this Id and insert it to the wifi_locations table, I'm getting an SQL Constraint violation error. There are no more details about what exactly went wrong.

Is there anything wrong with my schema? Is there a better way to achieve such modeling?

EDIT:

The exact error:

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

Answer 1

You should create FOREIGN KEY in your second table with will reference to PRIMARY KEY in Locations table. In SQLite FOREIGN KEYS are supported but implicitly not enabled so first you have to enable them.

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

in your 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 this all works since SQLite version 3.6.19. For more information have look at SQLite Foreign Key Support.