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 circular references between tables).
So to summarize, the strategy for modifying a table with foreign
constraints enabled is to:
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 on the tables
to get the correct insertion order.
3b. If circular dependencies, either use deferred constraints or come
up with a sophisticated algorithm to reinsert the original data (needs
to take into account not null columns with circular references
present).
4. Drop all the original tables, again with similar steps to 3a and 3b.
5. Rename all the temporary tables to their original names.
I will try this algorithm today and report back if I fail. Since I
don't have time to imagine an algorithm to delete/insert/update rows
in an order that doesn't break constraints, I've ended up using
deferred constraints (undesirable in my case) just to support table
modification. It would be great if the kind of complexity above was
somehow encapsulated in the database engine, instead of having users
work around it with non-trivial steps. As a side note, the above
algorithm isn't likely to be particularly performant on databases with
significant data present. In the general case of multiple individual
modifications to tables (such as in the context of a database change
manangement framework), the amount of work being done by the DB to
modify the table is quite time consuming.
Thanks for your help with this.
Patrick Earl
On Mon, May 10, 2010 at 5:18 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> 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 ParkingLot (Id) VALUES (1);
>> INSERT INTO Car (Id, ParkingLotId) VALUES (2, 1);
>>
>> BEGIN TRANSACTION;
>> CREATE TABLE ParkingLotTemp (Id int NOT NULL PRIMARY KEY);
>> INSERT INTO ParkingLotTemp (Id) SELECT Id FROM ParkingLot;
>> DROP TABLE ParkingLot;
>> ALTER TABLE ParkingLotTemp RENAME TO ParkingLot;
>> COMMIT TRANSACTION;
>>
>> Even though at the end of the transaction you can select and find the
>> appropriate rows in the car and parking lot tables, committing the
>> transaction causes a foreign constraint violation.
>
> I'm not sure how you expected this to work. You declare ParkingLot as a
> parent table of Car, but then you DROP TABLE ParkingLot, leaving Car an
> orphan. The only legitimate way to do this is to DROP TABLE Car first, or to
> remove the foreign key constraint from it (which SQLite doesn't let you do).
> The fact that you rename another table 'ParkingLot' later has nothing to do
> with your constraint: the constraint is linked to the table, not to the
> table's name.
>
> If you're going to make a temporary copy of ParkingLot, then make a temporary
> copy of Car too:
>
> CREATE TABLE CarTemp (Id int NOT NULL PRIMARY KEY, ParkingLotId int
> NOT NULL REFERENCES ParkingLotTemp (Id) DEFERRABLE INITIALLY DEFERRED);
> INSERT INTO CarTemp (Id) SELECT Id FROM Car;
>
> Then you can drop both original tables and rename both 'temp' tables.
> However, I don't see why you're doing any of this rather than just adding and
> removing rows from each table as you need.
>
> Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
