Is there Such Thing As The Opposite of Unique Constraint
Subqueries are not allowed in a CHECK constraint, or in the WHEN clause of a trigger, but you can use them inside a trigger function, something like this:

CREATE FUNCTION my_little_trigger_function()
RETURNS TRIGGER
AS $$
DECLARE
   row_count int;
BEGIN
   row_count := (SELECT COUNT(*)
                 FROM ...
                 WHERE ... = NEW.x ...);
   IF (row_count = 0) THEN
       RAISE EXCEPTION 'I''m sorry, Dave. I''m afraid I can''t do that.';
   END IF;
   RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_little_trigger
BEFORE INSERT ON my_little_table
FOR EACH ROW
EXECUTE PROCEDURE my_little_trigger_function();