Executing the following query is very fast (75ms), but removing the comment from the last row it becomes slow (2.5s), and the time increases exponentially with the number of rows in the table.

It creates a table with a foreign key constraint and inserts 8000 random values, with the foreign key always NULL.

The 8000 rows are inserted very quickly, and it is possible to query them very quickly.

But it's impossible to drop the table for a few seconds.

PRAGMA foreign_keys = 0;


CREATE TABLE Drawings ( 
    PartNumber       TEXT,
    Description      TEXT NOT NULL,
    ParentPartNumber TEXT,
    FOREIGN KEY ( ParentPartNumber ) REFERENCES Drawings ( PartNumber ) 

CREATE UNIQUE INDEX PartNumber_Idx ON Drawings ( 

INSERT INTO Drawings ( 
    cte(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cte LIMIT 8000)
  SELECT x, random() FROM cte;

PRAGMA foreign_keys = 1;

  FROM Drawings;



I just found out that disabling the foreign key check while dropping the table speeds up the operation:

PRAGMA foreign_keys = 0;
PRAGMA foreign_keys = 1;

The question is still open: why dropping a table (or performing other operations that I haven't identified yet) cannot be done immediately after populating the table above, but it can be done after leaving the database alone for a few seconds or after disabling the foreign key check?

  • Ok, I'll ask: why would you drop the table?
    – Hannah Vernon
    Commented Jul 9, 2014 at 17:52
  • No reason in the real world (so far). I am studying Sqlite, and I was running a test script that starts with deleting and recreating the tables, and the script was hanging. If the problem is only with dropping the table immediately after creating and populating it then I'm OK, but I want to know if there is something that will prevent me from doing other things before designing the whole database structure.
    – stenci
    Commented Jul 9, 2014 at 19:06
  • @MaxVernon: I went back working on the full script, and it hangs without dropping any table. I don't know where it hangs because it's very long. The script above is the result of 1 hour of shortening the script to get it to an easily reproducible form.
    – stenci
    Commented Jul 9, 2014 at 19:23
  • Surely you wouldn't actually do this type of thing in real life; why worry about something that will never happen?
    – Hannah Vernon
    Commented Jul 10, 2014 at 4:20
  • @MaxVernon: The same problem happens in real life examples that are difficult to describe here because they involve many tables with many foreign keys and importing data from an existing database instead of random numbers. I hope that understanding why the drop table is delayed in this simplified case, I can fix also the real world case.
    – stenci
    Commented Jul 10, 2014 at 6:15

1 Answer 1


The documentation says:

Indices are not required for child key columns but they are almost always beneficial. Each time an application deletes a row from the parent table, it performs [some query]. If this SELECT returns any rows at all, then SQLite concludes that deleting the row from the parent table would violate the foreign key constraint and returns an error. If these queries cannot use an index, they are forced to do a linear scan of the entire child table. In a non-trivial database, this may be prohibitively expensive.

So, in most real systems, an index should be created on the child key columns of each foreign key constraint. The child key index does not have to be (and usually will not be) a UNIQUE index.

You should create an index on Drawings(ParentPartNumber).

  • Thanks, I will try your suggestion as soon as I'm back home from vacation, in 2 weeks (yay!). But if your answer is correct, then I would expect the delete to be always slow. Instead it is slow ONLY if the delete is executed right after the previous commands. If I execute all but the delete, then wait a few seconds, then execute the delete, it's fast. Are there background activities going on? If yes, why are they blocking the database?
    – stenci
    Commented Jul 12, 2014 at 22:56
  • I did mark this as an answer because it solves the problem described, but it doesn't really answer the question I added in the edit: "why is there a delay"
    – stenci
    Commented Jul 28, 2014 at 14:28
  • See the last two sentences of the first paragraph.
    – CL.
    Commented Jul 28, 2014 at 14:34
  • The last phrases mention a possible performance problem at the drop time, but if I wait a few seconds between inserting and dropping, then the drop is fast. I understand that adding the index solves the problem, and I understand why it's faster. I don't understand why, after inserting the rows, I can immediately select rows (so the database is available), I can wait a few seconds and quickly drop the table (so the lack of the index doesn't make dropping a table sensitively slower), but I cannot immediately drop the table (because the database seems to be busy for some reason).
    – stenci
    Commented Jul 28, 2014 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.