Re: [sqlite] Foreign constraints and table recreation

2010-05-11 Thread Simon Slavin
On 11 May 2010, at 8:09am, Patrick Earl wrote: > sqlite> begin transaction; > sqlite> > sqlite> DROP TABLE "ParkingLotLevel"; > sqlite> DROP TABLE "Car"; > sqlite> DROP TABLE "ParkingLot"; > sqlite> > sqlite> Commit transaction; > Error: foreign key constraint failed > > And now, we switch Car a

Re: [sqlite] Foreign constraints and table recreation

2010-05-11 Thread Patrick Earl
Okay, I tried the strategy discussed previously but I'm still having problems. Either I'm not seeing something, or there's a bug in the foreign constraint support. Take a look at the following two execution snippets: sqlite> sqlite> commit transaction; sqlite> sqlite> begin transaction; sqlite>

Re: [sqlite] Foreign constraints and table recreation

2010-05-10 Thread Patrick Earl
On Mon, May 10, 2010 at 10:58 AM, Simon Slavin wrote: > > It should not be possible to have circular dependencies.  Because you somehow > got the data in in the first place, and /that/ wouldn't have been possible > had you had circular dependencies.  Part of normalising your data structure > in

Re: [sqlite] Foreign constraints and table recreation

2010-05-10 Thread Simon Slavin
On 10 May 2010, at 5:32pm, Patrick Earl wrote: > 1. Find all direct and indirect dependants of the table being modified. > 2. Create temporary tables for all of these. > 3. Copy the data from the main tables into these temporary tables. > 3a. If no circular dependencies, do a topological sort

Re: [sqlite] Foreign constraints and table recreation

2010-05-10 Thread Patrick Earl
Thanks Simon. I believe you're correct in that I can recreate all dependant tables. I had attempted this trick earlier, but was doing so in the context of immediate mode constraints, and that made the re-insertion of data and dropping of tables exceptionally complicated in some cases (such as cir

Re: [sqlite] Foreign constraints and table recreation

2010-05-10 Thread Simon Slavin
On 10 May 2010, at 7:34am, Patrick Earl wrote: >PRAGMA foreign_keys = ON; > >CREATE TABLE ParkingLot (Id int NOT NULL PRIMARY KEY); >CREATE TABLE Car (Id int NOT NULL PRIMARY KEY, ParkingLotId int > NOT NULL REFERENCES ParkingLot (Id) DEFERRABLE INITIALLY DEFERRED); >INSERT INTO

[sqlite] Foreign constraints and table recreation

2010-05-09 Thread Patrick Earl
Greetings all. I've been having much trouble with foreign constraints and updating the table schema. I used to just recreate the table and rename, but now that I wish to enable foreign keys on my connections, it seems that I can no longer do this. The following statements produce a foreign const