在PostgreSQL中添加需要来自另一个表的信息的约束

Question

我在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,
);

我如何执行约束studentmarks.marks_scored <= exam.total_marks，使行为与CHECK约束一样？

Answer 1

使用trigger。

您需要首先创建trigger函数。

-- 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;

然后创建trigger。

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

注意我在postgres中进行了测试