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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
