Detect foreign key constraint fail on sqlite using trigger

foreign_key.sql

PRAGMA foreign_keys = ON;

CREATE TABLE parent (id TEXT PRIMARY KEY) STRICT;
INSERT INTO parent (id) VALUES ("PARENT_FOO");

CREATE TABLE school (id TEXT PRIMARY KEY) STRICT;
INSERT INTO school (id) VALUES ("SCHOOL_FOO");  

CREATE TABLE child (
  parent_id TEXT,
  school_id TEXT
) STRICT;

DROP TRIGGER IF EXISTS check_parent_fk;
DROP TRIGGER IF EXISTS check_school_fk;

CREATE TRIGGER check_parent_fk
BEFORE INSERT ON child
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'Invalid parent_id: Referenced parent does not exist')
  WHERE NOT EXISTS (SELECT 1 FROM parent WHERE id = NEW.parent_id);
END;

CREATE TRIGGER check_school_fk
BEFORE INSERT ON child
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'Invalid school_id: Referenced school does not exist')
  WHERE NOT EXISTS (SELECT 1 FROM school WHERE id = NEW.school_id);
END;

INSERT INTO child (parent_id, school_id) VALUES ("PARENT_FOO", "SCHOOL_FOO");
INSERT INTO child (parent_id, school_id) VALUES ("PARENT_FOO", "SCHOOL_INV");
INSERT INTO child (parent_id, school_id) VALUES ("PARENT_INV", "SCHOOL_FOO");

SELECT * FROM child;