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