sqlite foreign key

今天在試著用AR去delete一筆資料時，發現明明就有設定FK，
去SQLITE manager裡刪的話，沒問題，delete跟update都會一起CASCADE，
而且database的foreign key = on, 但在php刪卻沒有CASCADE，
百思不得其解時，到了sqlite官網一看，原來SQLITE的FK是設心酸的...
要設Trigger才會發揮FK的效用，
SQLITE manager是自動「幫忙」刪的，
Mysql的InnoDB引擎就沒這問題，真是被擺了一道。



在album加了二個trigger:

CREATE TRIGGER [fkd_album_file_album_id]
BEFORE DELETE
ON [album]
FOR EACH ROW
BEGIN
DELETE from album_file WHERE dir_id = OLD.id;
END;


CREATE TRIGGER [fku_album_file_album_id]
BEFORE UPDATE
ON [album]
FOR EACH ROW
BEGIN
UPDATE album_file SET dir_id = NEW.id WHERE dir_id = OLD.id;
END;

reference:
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
http://www.sqlite.org/foreignkeys.html
http://justatheory.com/computers/databases/sqlite/foreign_key_triggers.html