0

I'm reading the book Android Database Programming

And I'm wondering to know if what said here is correct:

for those who are well versed in SQL programming and database schemas, you might be wondering if it's possible to add triggers and key constraints to your SQLite database schemas. The answer is, "yes, you can use triggers but no, you cannot use foreign key constraints."

and also:

remember that Android's SQLite database doesn't support key constraints

This is strange for me, because I know that I can use (from SQLite 3.6.19)

setForeignKeyConstraintsEnabled method for enabling FK constraints, or I can follow this

I'm confused. So the question is: Why the book aforementioned said that FK are not supported? Considering that at time of publishing (June 2012), Android 4.0.3 (SQLite 3.7.4) was already released.

Community
  • 1
  • 1
GVillani82
  • 17,196
  • 30
  • 105
  • 172

1 Answers1

1

There might be many reasons for the author not to mention that (EG: the book could have been written when the current version of the SQLite was older than 3.6.19 and published later), but it doesn't make any sense to answer/guess that in StackOverflow.

What it does make sense to mention is the fact that Foreign Key support was added in 3.6.19 but it doesn't just work out of the blue. There are some things to take into consideration in order to use them:

  • SQLite version should be >= 3.6.19
  • The library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined
  • Support for FK must be enabled by the application at runtime, using the PRAGMA foreign_keys command

It should also be noted that FK constraints are disabled by default for backwards compatibility. So you should take care of enabling them for each database connection.

You can read more about this in the official documentation.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • In some points in the book, the author talk about API Level 11,(Honeycomb) that uses SQLite 3.7.4. – GVillani82 Oct 28 '13 at 12:33
  • Interesting. Anyway, having SQLite >= 3.6.19 is only one of the 3 requirements to work with FK. He still could have compiled his version with the `SQLITE_OMIT_FOREIGN_KEY` flag. – Mosty Mostacho Oct 28 '13 at 13:26