最近在做数码相框上的嵌入式开发,开发过程中使用的SQLite数据库,但是编码的过程中,遇到个问题,SQLite不支持外键约束,外键约束会被解析但不会被执行。
参考了网上的做法,自己做了个实验,用触发器来实现了SQLite的外键约束。
建表语句:
CREATE TABLE jokeItem
(
id integer primary key,
content TEXT,
classId integer
);
CREATE TABLE jokeClass
(
classId integer primary key,
className TEXT
);
创建插入触发器:
CREATE TRIGGER FK_INSERT
BEFORE INSERT ON jokeItem
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK,'No this classId in jokeClass')
WHERE (SELECT classId FROM jokeClass WHERE classId = NEW.classId) IS NULL;
END;
插入操作的外键支持效果:
[img]http://dl.iteye.com/upload/attachment/201904/f08a2033-2407-3244-87b2-5fc58af48c59.jpg[/img]
创建更新触发器:
CREATE TRIGGER FK_UPDATE
BEFORE UPDATE ON jokeItem
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK,'No this classId in jokeClass')
WHERE (SELECT classId FROM jokeClass WHERE classId = NEW.classId) IS NULL;
END;
更新操作的外键支持效果:
[img]http://dl.iteye.com/upload/attachment/201906/69316e16-e73a-3761-8083-300cdb5eea38.jpg[/img]
创建删除触发器:
CREATE TRIGGER FK_DELETE
BEFORE DELETE ON jokeClass
FOR EACH ROW BEGIN
DELETE FROM jokeItem WHERE classId = OLD.classId;
END;
删除操作的外键支持效果:
[img]http://dl.iteye.com/upload/attachment/201991/24f40042-79f2-3a6b-b8ef-e63033bd781e.png[/img]
参考了网上的做法,自己做了个实验,用触发器来实现了SQLite的外键约束。
建表语句:
CREATE TABLE jokeItem
(
id integer primary key,
content TEXT,
classId integer
);
CREATE TABLE jokeClass
(
classId integer primary key,
className TEXT
);
创建插入触发器:
CREATE TRIGGER FK_INSERT
BEFORE INSERT ON jokeItem
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK,'No this classId in jokeClass')
WHERE (SELECT classId FROM jokeClass WHERE classId = NEW.classId) IS NULL;
END;
插入操作的外键支持效果:
[img]http://dl.iteye.com/upload/attachment/201904/f08a2033-2407-3244-87b2-5fc58af48c59.jpg[/img]
创建更新触发器:
CREATE TRIGGER FK_UPDATE
BEFORE UPDATE ON jokeItem
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK,'No this classId in jokeClass')
WHERE (SELECT classId FROM jokeClass WHERE classId = NEW.classId) IS NULL;
END;
更新操作的外键支持效果:
[img]http://dl.iteye.com/upload/attachment/201906/69316e16-e73a-3761-8083-300cdb5eea38.jpg[/img]
创建删除触发器:
CREATE TRIGGER FK_DELETE
BEFORE DELETE ON jokeClass
FOR EACH ROW BEGIN
DELETE FROM jokeItem WHERE classId = OLD.classId;
END;
删除操作的外键支持效果:
[img]http://dl.iteye.com/upload/attachment/201991/24f40042-79f2-3a6b-b8ef-e63033bd781e.png[/img]