1

I have a SQLite table that looks like this:

CREATE TABLE myTable (
    _id integer primary key autoincrement,
    ...
    ... /* some useful fields */
    ...
    parent integer,
    FOREIGN KEY (parent) REFERENCES myTable (_id)
);

Some of the entries in myTable have a parent, others don't. Business logic ensures that there are no "circles". I'm using PRAGMA foreign_keys = ON, so that foreign key violations are detected.

Question: If I want to delete the whole table, is it enough to execute DELETE FROM myTable? Or do I need to delete the "leaves" first, then their parents, etc., all the way up to avoid foreign key violations?

I've tested it and using just DELETE FROM myTable seems to work. However, I want to know whether this is just a coincidence or something I can rely upon (i.e. documented and expected behaviour).

EDIT: I don't have ON DELETE CASCADE enabled, and this is on purpose.

Heinzi
  • 167,459
  • 57
  • 363
  • 519

1 Answers1

0

Insert

By specifying a foreign key and with PRAGMA foreign_keys = ON, starting from an empty table, SQLite will deny you any entry of data (except identities, where parent = _id) with the message:

Error: foreign key constraint failed.

So from the start, you need to make at least one entry with PRAGMA foreign_keys = OFF or a "circular" entry.

Deletion of specific entries

If you do not provide an action such as CASCADE to your foreign key, SQLite will deny deletion of a "parent entry" if the parent has children due to the foreign key constraint. This is the default behaviour, equivalent to "NO ACTION".

Deletion of all entries

Deletion of all entries is possible through DELETE FROM myTable and should not trigger the foreign key constraints on the same table, even if there is no cascade clause. I verified by test, it might not be documented.

MPelletier
  • 16,256
  • 15
  • 86
  • 137
  • 1
    Re your first point: Since `parent` is nullable, adding new rows with `parent = NULL` is possible even if `PRAGMA foreign_keys = ON`. Re deletion: Thanks for testing it; I'll leave the question open in case anyone finds this documented anywere (which is what I'm looking for). – Heinzi Oct 30 '12 at 06:16