SQLite現(xiàn)在的版本還不支持外鍵功能，雖然外鍵約束會(huì)被解析，但執(zhí)行的時(shí)候被忽略。但我們可以手動(dòng)實(shí)現(xiàn)外鍵，實(shí)現(xiàn)的原理就是觸發(fā)器。下面是我的實(shí)現(xiàn)方法。主要是針對一個(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表建立對PLU表的外鍵約束，這樣就可以實(shí)現(xiàn)CORE2數(shù)據(jù)需求中的要求，問題是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;

下面我們分別來作三個(gè)實(shí)驗(yàn)：

一、插入實(shí)驗(yàn)

首先我們在PLU里面插入一個(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)完全正確。