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?
Advertisement
Answer
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