CODEWITHSUNDEEP
sqlite
RGeneral24
RGeneral24

Reputation: 31

Some doubts on a constraint

I have 2 tables: 

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);

Each employee there is a bool column stating if he is active or not.

Every time I want to add another row to Services (via insert) I want to force that only employees with True in active are accepted.

I dunno how can I do this. If via check or by a trigger.

Upvotes: 1

Views: 46

Answers (1)

Samuel Neff
Samuel Neff

Reputation: 74909

You want to use a before trigger with a SELECT RAISE error if the employee is not active, like this:

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;

You can't do the same thing with a constraint because that would require a subquery which is not allowed in a constraint expression.

Full example with test data:

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);

Upvotes: 2

Related Questions