SQLite現在的版本還不支持外鍵功能，雖然外鍵約束會被解析，但執行的時候被忽略。但我們可以手動實現外鍵，實現的原理就是觸發器。下面是我的實現方法。主要是針對一個例子：
先看下面兩個表。

CREATE TABLE PLU (PluID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Property text,
Price double NOT NULL,
Left integer NOT NULL,
Department text,
Other text);

CREATE TABLE PluSuit (SuitID integer NOT NULL PRIMARY KEY,
Price double NOT NULL,
Property text,
Name text NOT NULL,
PluID integer NOT NULL CONSTRAINT fk_plu_id REFERENCES PLU(PluID) ON DELETE CASCADE,
Numbers integer NOT NULL)
這樣就為PluSuit表建立對PLU表的外鍵約束，這樣就可以實現CORE2數據需求中的要求，問題是SQLite不執行這個約束，所以這樣創建以后，我們還要再創建三個觸發器，INSERT,UPDATE,DELETE觸發器：

BEFORE INSERT ON PluSuit
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "PluSuit" violates foreign key constraint "fk_plu_id"')
WHERE (SELECT PluID FROM PLU WHERE PluID = NEW.PluID) IS NULL;
END;

BEFORE UPDATE ON PluSuit
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'update on table "PluSuit" violates foreign key constraint "fk_plu_id"')
WHERE (SELECT PluID FROM PLU WHERE PluID = NEW.PluID) IS NULL;
END;

CREATE TRIGGER fkd_plusuit_pluid
BEFORE DELETE ON PLU
FOR EACH ROW BEGIN
DELETE from PluSuit WHERE PluID = OLD.PluID;
END;

下面我們分別來作三個實驗：
一、插入實驗
首先我們在PLU里面插入一個數據（一雙anta運動鞋的信息）：
insert into PLU values(1,'anta','sport',299,100,'sales','ok');
insert into PLU values(3,'nike','sport',699,200,'sales','ok');
然后我們開始在PluSuit里面插入一個數據（兩雙一起打折賣）：
insert into PluSuit values(100,350,'old','anta',1,2);成功了
insert into PluSuit values(100,350,'old','anta',2,2);失敗，得到正確的錯誤信息

更新實驗
update PluSuit set PluID=2 where SuitID=100;失敗，得到正確的錯誤信息
update PluSuit set PluID=3 where SuitID=100;成功

刪除實驗
delete from PLU where PluID=1;
查看PluSuit中數據被正確刪除。
實驗結果，觸發器的實現完全正確。

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1604490