CREATE FUNCTION uid_shards_fk_check_fn() RETURNS trigger AS
$body$
BEGIN
    -- because shards_confab in herits from shards select from shards
    -- checks against shards_confab too
    IF SELECT NOT EXISTS(SELECT uid FROM shards WHERE uid = NEW.uid_shards) THEN
        RAISE EXCEPTION '% doesn\'t have a valid foreign key into shards', NEW.name;

    END IF;
    RETURN NEW;
END
$body$
LANGUAGE 'plpgsql';


CREATE TRIGGER uid_shards_fk_check_trig BEFORE INSERT ON collections
FOR EACH ROW
    EXECUTE PROCEDURE uid_shards_fk_check_fn();


CREATE TRIGGER uid_shards_fk_check_trig BEFORE INSERT ON samples
FOR EACH ROW
    EXECUTE PROCEDURE uid_shards_fk_check_fn();

CREATE FUNCTION shards_delete_fn() RETURNS trigger AS
$body$
BEGIN
    EXECUTE 'DELETE FROM samples WHERE uid_shards=$1' USING OLD.uid;
    EXECUTE 'DELETE FROM collections WHERE uid_shards=$1' USING OLD.uid;
    RETURN OLD;
END
$body$
LANGUAGE 'plpgsql';

CREATE TRIGGER shards_delete BEFORE DELETE ON shards
FOR EACH ROW
    EXECUTE PROCEDURE shards_delete_fn();

CREATE TRIGGER shards_confab_delete BEFORE DELETE ON shards_confab
FOR EACH ROW
    EXECUTE PROCEDURE shards_delete_fn();