我正在尝试创建具有特定限制的表,并且不允许使用触发器。
我使用的是Oracle SQL。
限制是:
- 每个课程ID只有1节课
- 学生最多可选修4门课程
- 课程最多允许25名学生
- 每门课程由1名讲师教授
- 每个学生只有一个专业
粗体2和3是我遇到问题的地方。
CREATE TABLE STUDENT(
STUDENT_ID NUMERIC(9),
STUDENT_NAME VARCHAR(32) NOT NULL,
STUDENT_ADDRESS VARCHAR(50),
MAJOR VARCHAR(4),
CONSTRAINT student_id_pk PRIMARY KEY(STUDENT_ID)
);
CREATE TABLE COURSE(
COURSE_ID VARCHAR(6),
COURSE_TITLE VARCHAR(32) NOT NULL,
INSTRUCTOR_ID NUMERIC(3) UNIQUE,
CONSTRAINT course_id_pk PRIMARY KEY(COURSE_ID),
CONSTRAINT instructor_id_fk FOREIGN KEY (INSTRUCTOR_ID) REFERENCES INSTRUCTOR(INSTRUCTOR_ID)
);
CREATE TABLE INSTRUCTOR(
INSTRUCTOR_ID NUMERIC(3),
INSTRUCTOR_NAME VARCHAR(50) NOT NULL,
INSTRUCTOR_OFFICE VARCHAR(4),
CONSTRAINT instructor_id_pk PRIMARY KEY(INSTRUCTOR_ID)
);
CREATE TABLE STU_CRSE(
STUDENT_ID NUMERIC (9),
COURSE_ID VARCHAR(6),
GRADE CHAR(1),
CONSTRAINT grade_range CHECK(GRADE IN ('A','B','C','D','F')),
CONSTRAINT stu_crs_pk PRIMARY KEY (STUDENT_ID,COURSE_ID),
CONSTRAINT crs_fk FOREIGN KEY (COURSE_ID) REFERENCES COURSE(COURSE_ID),
CONSTRAINT str_fk FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(STUDENT_ID),
--
--Below are the constraints I'm having trouble with.
--
CONSTRAINT stu_limit CHECK (COUNT(STU_CRSE.STUDENT_ID)<5),
CONSTRAINT stu_crse_limit CHECK (count(STU_CRSE.STUDENT_ID,STU_CRSE.COURSE_ID)<26)
);
您可以尝试:
CREATE MATERIALIZED VIEW LOG ON STU_CRSE
WITH SEQUENCE, ROWID(course_id, student_id)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW STU_CRSE__NUM_STUDENTS__MV
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT COURSE_ID,
COUNT( STUDENT_ID ) AS num_students
FROM STU_CRSE
GROUP BY COURSE_ID;
ALTER TABLE STU_CRSE__NUM_STUDENTS__MV
ADD CONSTRAINT STU_CRSE__NUM_STU__MV__COUNT CHECK ( num_students <= 25 );
CREATE MATERIALIZED VIEW STU_CRSE__NUM_COURSES__MV
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT STUDENT_ID,
COUNT( COURSE_ID ) AS num_courses
FROM STU_CRSE
GROUP BY STUDENT_ID;
ALTER TABLE STU_CRSE__NUM_COURSES__MV
ADD CONSTRAINT STU_CRSE__NUM_CRS__MV__COUNT CHECK ( num_courses <= 4 );
测试:
INSERT INTO STU_CRSE
SELECT 1, LEVEL, 'A' FROM DUAL CONNECT BY LEVEL <= 4;
4 rows inserted.
COMMIT;
Commit complete.
INSERT INTO STU_CRSE VALUES (1, 5, 'A' );
1 row inserted.
COMMIT;
Error report -
SQL Error: ORA-12048: error encountered while refreshing materialized view "TEST"."STU_CRSE__NUM_COURSES__MV"
ORA-02290: check constraint (TEST.STU_CRSE__NUM_CRS__MV__COUNT) violated
一个简单的约束解决方案是:
将整数STUDENT_LFDNR和COURSE_LFDNR添加到STU_CRSE
在(STUDENT_ID、STUDENT_LFDNR)、(COURSE_ID、COURSE_LFDNR
在STUDENT_LFDNR(>0&<=4)上添加常量打印
在COURSE_LFDNR(>0&<=25)上添加约束
但与之合作将是痛苦的。。。