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?

What action do you want when a query sets exam.total_marks below an existing studentmarks.marks_scored ? — Serg
– Serg, Commented Jan 18, 2022 at 12:03
@Serg that should behave just like a CHECK (...) constraint in PostgreSQL — helperFunction
– helperFunction, Commented Jan 18, 2022 at 12:08

user8193706 · Accepted Answer · 2022-01-18 14:09:08Z

2

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

edited Jan 18, 2022 at 14:09

answered Jan 18, 2022 at 13:26

user8193706

2,4552 gold badges11 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding constraint in PostgreSQL that requires information from another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related