CREATE FUNCTION chk_master_detail() RETURNS TRIGGER AS $$
BEGIN
  PERFORM id FROM detail WHERE master_id = NEW.id;
  IF NOT FOUND THEN
    RAISE 'KEY VIOLATION!';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION chk_detail_master() RETURNS TRIGGER AS $$
DECLARE
 FND boolean;
BEGIN
  PERFORM id FROM master WHERE id = OLD.master_id;
  IF NOT FOUND THEN
    RETURN NEW;
  END IF;
  FND := EXISTS(SELECT id FROM detail WHERE master_id = OLD.master_id);
  IF NOT FND THEN
    RAISE 'KEY VIOLATION!';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TABLE master(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE detail(
  id integer NOT NULL PRIMARY KEY,
  master_id integer NOT NULL REFERENCES master(id)
);

CREATE CONSTRAINT TRIGGER master_detail_has_one AFTER INSERT OR UPDATE
  ON master
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW
  EXECUTE PROCEDURE chk_master_detail();

CREATE CONSTRAINT TRIGGER master_detail_has_one_detail_t AFTER UPDATE OR DELETE
  ON detail
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW
  EXECUTE PROCEDURE chk_detail_master();

CREATE FUNCTION schedule_commit_check() RETURNS TRIGGER AS $$
BEGIN
  CREATE TEMP TABLE IF NOT EXISTS on_commit_check (
    v boolean primary key 
  ) ON COMMIT DROP;

  UPDATE on_commit_check SET v = TRUE;
  IF NOT FOUND THEN
    CREATE CONSTRAINT TRIGGER master_detail_has_one AFTER INSERT
      ON on_commit_check
      DEFERRABLE INITIALLY DEFERRED
      FOR EACH ROW
      EXECUTE PROCEDURE chk_global_master_detail();

    INSERT INTO on_commit_check VALUES (true);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION chk_global_master_detail() RETURNS TRIGGER AS $$
DECLARE 
 fnd boolean;
BEGIN
  fnd := EXISTS(SELECT master.id 
    FROM master
    LEFT OUTER JOIN detail on master.id = detail.master_id
    WHERE detail.master_id IS NULL
  );
  IF FND     THEN
    RAISE 'M/1D RELATION VIOLATION!';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TABLE master(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE detail(
  id integer NOT NULL PRIMARY KEY,
  master_id integer NOT NULL REFERENCES master(id)
);

CREATE TRIGGER master_detail_has_one AFTER INSERT OR UPDATE
  ON master
  FOR EACH STATEMENT
  EXECUTE PROCEDURE schedule_commit_check();

CREATE TRIGGER master_detail_has_one_detail_t AFTER UPDATE OR DELETE
  ON detail
  FOR EACH STATEMENT
  EXECUTE PROCEDURE schedule_commit_check();