如何为SQL分配表实现学生选课数与项目人数限制约束

你原来的CHECK约束写法无法实现需求，因为行级CHECK只能校验当前行的字段值，没法统计每个学生或项目的关联记录总数。下面是两种可行的实现方案：

一、触发器实现（通用方案，适配多数数据库） #

触发器可以在插入/更新记录前，统计对应学生或项目的已分配数量，超过限制则阻止操作。以Oracle为例：

1. 限制每名学生最多参与2个项目 #

CREATE OR REPLACE TRIGGER trg_student_max_programs
BEFORE INSERT OR UPDATE ON Allocation
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM Allocation
    WHERE StudID = :NEW.StudID;
    IF v_count >= 2 THEN
        RAISE_APPLICATION_ERROR(-20001, '每名学生最多只能参与2个项目');
    END IF;
END;
/

2. 限制每个项目最多容纳40名学生 #

CREATE OR REPLACE TRIGGER trg_program_max_students
BEFORE INSERT OR UPDATE ON Allocation
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM Allocation
    WHERE ProgID = :NEW.ProgID;
    IF v_count >= 40 THEN
        RAISE_APPLICATION_ERROR(-20002, '每个项目最多只能容纳40名学生');
    END IF;
END;
/

二、数据库特定约束（以PostgreSQL为例） #

PostgreSQL支持在CHECK约束中调用自定义函数，通过函数统计关联记录数：

步骤1：创建校验函数 #

-- 校验学生参与项目数不超过2
CREATE FUNCTION check_student_program_count(p_studid INT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM Allocation WHERE StudID = p_studid) <= 2;
END;
$$ LANGUAGE plpgsql STABLE;

-- 校验项目容纳学生数不超过40
CREATE FUNCTION check_program_student_count(p_progid INT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM Allocation WHERE ProgID = p_progid) <= 40;
END;
$$ LANGUAGE plpgsql STABLE;

步骤2：给Allocation表添加约束 #

ALTER TABLE Allocation
ADD CONSTRAINT chk_student_max_programs CHECK (check_student_program_count(StudID)),
ADD CONSTRAINT chk_program_max_students CHECK (check_program_student_count(ProgID));

补充优化建议 #

记得给Allocation表添加联合主键，避免同一个学生重复分配到同一个项目：

ALTER TABLE Allocation ADD PRIMARY KEY (StudID, ProgID);

内容的提问来源于stack exchange，提问作者Takura Kurewaseka