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

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!