2015-02-03

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

沒有留言:

張貼留言