sqlite - 关于约束的一些疑问

Question

我有 2 张 table :

CREATE TABLE IF NOT EXISTS Service (
id_ser integer PRIMARY KEY,
description char(256),
id_emp integer NOT NULL,
FOREIGN KEY (id_emp) REFERENCES Employee(id_emp));

CREATE TABLE IF NOT EXISTS Employee (
id_emp integer PRIMARY KEY,
name char(50),
active boolean);

每个员工都有一个 bool 列，说明他是否活跃。

每次我想向服务添加另一行(通过插入)时，我都想强制只有 的员工真 在事件中被接受。

我不知道我该怎么做。如果通过检查或触发器。

Answer 1

您想使用带有 SELECT RAISE 的前触发器如果员工不活跃，则会出现错误，如下所示:

CREATE TRIGGER IF NOT EXISTS I_Service_Active 
    BEFORE INSERT ON Service
    BEGIN

        SELECT  RAISE(FAIL, 'Cannot insert Service record since Employee is not active')
        FROM    Employee E
        WHERE   E.id_emp = NEW.id_emp
          AND   NOT E.active;
    END;

你不能对约束做同样的事情，因为这需要一个约束表达式中不允许的子查询。

带有测试数据的完整示例:

CREATE TABLE IF NOT EXISTS Employee ( 
    id_emp integer PRIMARY KEY, 
    name char(50), 
    active boolean);

CREATE TABLE IF NOT EXISTS Service ( 
    id_ser integer PRIMARY KEY, 
    description char(256), 
    id_emp integer NOT NULL, 
    FOREIGN KEY (id_emp) REFERENCES Employee(id_emp)); 

CREATE TRIGGER IF NOT EXISTS I_Service_Active 
    BEFORE INSERT ON Service
    BEGIN

        SELECT  RAISE(FAIL, 'Cannot insert Service record since Employee is not active')
        FROM    Employee E
        WHERE   E.id_emp = NEW.id_emp
          AND   NOT E.active;
    END;

INSERT INTO Employee (id_emp, name, active)
VALUES (1, 'Sam', 1);

INSERT INTO Employee (id_emp, name, active)
VALUES (2, 'John', 0);

-- SUCCEEDS
INSERT INTO Service (id_ser, description, id_emp)
VALUES (1, 'Sam Service', 1);

-- FAILS
INSERT INTO Service (id_ser, description, id_emp)
VALUES (2, 'John Service', 2);