SQLite用触发器来替代外键约束

最新推荐文章于 2022-06-15 18:07:31 发布

aisq

最新推荐文章于 2022-06-15 18:07:31 发布

阅读量2.4k

点赞数

分类专栏：数据库文章标签： sqlite integer null insert delete each

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SQLite用触发器来替代外键约束

下面文章出处：

http://hi.baidu.com/wastorode/blog/item/3c8bfefe7460e8c9b48f318e.html

用触发器实现SQLite的外键约束

    最近在做数码相框上的嵌入式开发，开发过程中使用的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;

    插入操作的外键支持效果：

    创建更新触发器：
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;

    更新操作的外键支持效果：

    创建删除触发器：
CREATE TRIGGER FK_DELETE
BEFORE DELETE ON jokeClass
FOR EACH ROW BEGIN
DELETE FROM jokeItem WHERE classId = OLD.classId;
END;

    删除操作的外键支持效果：

原文出处：http://edu.codepub.com/2010/0318/21120.php

CREATE TABLE [Category] (
[Pkid] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[CategoryName] NVARCHAR(32) NOT NULL,
[CategoryGuid] char(36) UNIQUE NOT NULL,
[CategoryDesc] nvarchar(256) NULL
)

CREATE TABLE [Product] (
[Pkid] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[CategoryPkid] INTEGER NOT NULL,
[ProductName] nvarchar(32) NULL,
[ProductPrice] NUMERIC(19,4) DEFAULT '0.00' NOT NULL
)

--Insert约束
CREATE TRIGGER FK_Product_CategoryPkid_Insert
BEFORE Insert ON Product
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK,'No this categoryPkid in category')
WHERE (SELECT Pkid FROM Category WHERE Pkid = NEW.CategoryPkid) IS NULL;
END

--Update约束
CREATE TRIGGER FK_Product_CategoryPkid_Update
BEFORE Update ON Product
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK,'No this categoryPkid in category')
WHERE (SELECT Pkid FROM Category WHERE Pkid = NEW.CategoryPkid) IS NULL;
END

原文出处：http://www.byz5.com/html/236/2/2418/1.htm

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);失败，得到正确的错误信息