SQLite現(xiàn)在的版本還不支持外鍵功能，雖然外鍵約束會(huì)被解析，但執(zhí)行的時(shí)候被忽略。但我們可以手動(dòng)實(shí)現(xiàn)外鍵，實(shí)現(xiàn)的原理就是觸發(fā)器。下面是我的實(shí)現(xiàn)方法。主要是針對(duì)一個(gè)例子：
先看下面兩個(gè)表。

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表建立對(duì)PLU表的外鍵約束，這樣就可以實(shí)現(xiàn)CORE2數(shù)據(jù)需求中的要求，問(wèn)題是SQLite不執(zhí)行這個(gè)約束，所以這樣創(chuàng)建以后，我們還要再創(chuàng)建三個(gè)觸發(fā)器，INSERT,UPDATE,DELETE觸發(fā)器：

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;

下面我們分別來(lái)作三個(gè)實(shí)驗(yàn)：
一、插入實(shí)驗(yàn)
首先我們?cè)赑LU里面插入一個(gè)數(shù)據(jù)（一雙anta運(yùn)動(dòng)鞋的信息）：
insert into PLU values(1,'anta','sport',299,100,'sales','ok');
insert into PLU values(3,'nike','sport',699,200,'sales','ok');
然后我們開始在PluSuit里面插入一個(gè)數(shù)據(jù)（兩雙一起打折賣）：
insert into PluSuit values(100,350,'old','anta',1,2);成功了
insert into PluSuit values(100,350,'old','anta',2,2);失敗，得到正確的錯(cuò)誤信息

更新實(shí)驗(yàn)
update PluSuit set PluID=2 where SuitID=100;失敗，得到正確的錯(cuò)誤信息
update PluSuit set PluID=3 where SuitID=100;成功

刪除實(shí)驗(yàn)
delete from PLU where PluID=1;
查看PluSuit中數(shù)據(jù)被正確刪除。
實(shí)驗(yàn)結(jié)果，觸發(fā)器的實(shí)現(xiàn)完全正確。

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