1

In SQLite for iOS (3.7.7) I am running these queries:

PRAGMA foreign_keys = ON;

create table venue(id integer primary key not null, name text not null);

create table event(id integer primary key not null, name text not null, 
venue_id integer references venue(id) on delete cascade);

But when I delete a venue, the children events are not deleted. Any ideas?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Piotr
  • 4,813
  • 7
  • 35
  • 46
  • I can't reproduce that behavior in 3.7.4. Are you sure `PRAGMA foreign_keys = ON` is set before deleting a venue? (It's a connection setting, not a database setting.) – Mike Sherrill 'Cat Recall' Nov 02 '11 at 18:53
  • That is odd. I did make the mistake of not setting it on every connection, but when I fixed that it did not help. Did you try this in the iPhone simulator? Because I get this to work in navicat for sqlite, but not in the simulator. – Piotr Nov 02 '11 at 19:12
  • Ok, I just tried it with most simple code possible in the simulator and it worked, so the error must be somewhere else. Thanks. – Piotr Nov 02 '11 at 19:32
  • When you figure it out, answer your own question, and accept your answer. Might happen to others. – Mike Sherrill 'Cat Recall' Nov 02 '11 at 21:53
  • Also, I wasn't using the iPhone simulator. When things go wrong, divide and conquer. Using the SQLite command interpreter is an easy way to isolate the problem to either the database or, um, everything else. – Mike Sherrill 'Cat Recall' Nov 02 '11 at 22:03
  • Possible duplicate of [Foreign key constraints in Android using SQLite? on Delete cascade](https://stackoverflow.com/questions/2545558/foreign-key-constraints-in-android-using-sqlite-on-delete-cascade) – StayOnTarget Dec 20 '17 at 21:24

2 Answers2

5

I followed what Catcall said and it worked for me: setting foreign_keys by running 

stmt.execute("PRAGMA foreign_keys = ON");

each time I establish a connection to the database.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Matheus Z
  • 81
  • 1
  • 2
0

The problem seems to be the usage of multiple handles in different threads although synchronized. Since sqlite included in iOS is new enough to allow one handle to be used across threads as long as they are properly synchronized, using one single handle instead of several should make this a no-problem. But I would be interested in getting to know how and if someone else solves this problem.

Piotr
  • 4,813
  • 7
  • 35
  • 46