Adding constraint in PostgreSQL that requires information from another table

Question

I have the following schema in PostgreSQL

CREATE TABLE exam (
    exam_id INT,
    exam_name TEXT,
    total_marks INT,
    PRIMARY KEY(exam_id)
);

CREATE TABLE studentmarks (
    studentmarks_id INT,
    student_id INT,
    exam_id INT,
    marks_scored INT,
    PRIMARY KEY(studentmarks_id),
    FOREIGN KEY(exam_id) REFERENCES exam ON DELETE SET NULL,
);

How can I enforce the constraint that studentmarks.marks_scored <= exam.total_marks such that the behaviour is just like the CHECK constraint?

Answer 1

Use trigger.

You need to create trigger function first.

-- FUNCTION: public.check_marks_calculation()

-- DROP FUNCTION public.check_marks_calculation();

CREATE FUNCTION public.check_marks_calculation()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
_exam_id integer;
_total_marks integer;
BEGIN
IF (TG_OP = 'INSERT') THEN
_exam_id = NEW.exam_id;
_total_marks = (select total_marks from exam where exam_id=_exam_id);
if(NEW.marks_scored <= _total_marks) then
RETURN NEW;
else
raise exception 'Student Marks greater than exam Total Marks.';
end if;
end if;
END;
$BODY$;

ALTER FUNCTION public.check_marks_calculation()
    OWNER TO postgres;

Then create trigger.

CREATE TRIGGER check_toal_marks
    BEFORE INSERT
    ON public.studentmarks 
    FOR EACH ROW
    EXECUTE FUNCTION public.check_marks_calculation();

NOTE I have tested in postgres