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 constraint violation, which seems to contradict the current
documentation, which says that dropping the table with deferred
constraints will only produce a violation if not corrected by the end
of the transaction.
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 used to be able to do all my DDL inside of transactions to ensure
sanity was preserved, but I can't figure out how to make that work any
longer. It seems I'd have to drop out of a transaction, turn off the
foreign keys, do the DDL, then turn the foreign keys back on.
I'm not sure if the above behavior was by design or not, but it's not
even ideal using deferred constraints and drop/rename to put the table
back. The following potential features may also help contribute to a
solution.
1. Allow ALTER TABLE to add/remove foreign constraints.
2. Allow foreign constraints to be deferred for the duration of a
transaction. (ie. SET CONSTRAINTS)
3. Allow foreign keys to be disabled / enabled within transactions
using pragma.
4. Allow tables to be renamed without causing foreign references to
that table to rename. You could then rename your original table,
create a new one in its place, and put all the original data back.
In any case, I'm looking forward to some sort of improvement to the
situation. Maybe I'm missing something, but I've spent my weekend
banging my head against this one.
Best regards,
Patrick Earl
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
