SQLite外键的实现
SQLite外键的实现
?
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触发器：
?create trigger plusuit_insert_trigger
?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;
?
?CREATE TRIGGER plusuit_update_trigger
?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中数据被正确删除。
实验结果，触发器的实现完全正确。?
?
转：http://www.sqlite.com.cn/MySqlite/6/403.Html
?
