On 23/03/2011, at 1:17 PM, Sam Carleton wrote: > I don't see any examples on http://www.sqlite.org/foreignkeys.html how to > either add or drop a foreign key to an existing table. What might that > syntax look like exactly?
You have to drop the old table and create a new one with the changed foreign keys. If you have data in the table, you'll want to back it up fist, like this: begin immediate; create temp table "My Table Backup" as select * from "My Table"; drop table "My Table"; create table "My Table" (<new definition here with foreign keys>); insert into "My Table" select * from "My Table Backup"; drop table "My Table Backup"; commit; Unfortunately SQLite doesn't automatically rollback a transaction if the create or drop statements fail. So you have to detect any errors and, if so, issue a rollback (instead of commit). > Also, from a performance perspective, is there an advantage to using a > foreign key in SQLite verses just an index? (aka, is it worth my time to add > the key to begin with, I understand there are draw backs). An index and a foreign key serve different purposes. What are you trying to achieve. Post the relevant parts of your schema. Thanks, Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
