CODEWITHSUNDEEP
sqloracle-databasevalidationconstraints
NonCreature0714
NonCreature0714

Reputation: 6014

Constraining Tables Based On FK Relationships

I'm trying to create tables with specific limitations, and I'm not allowed to use triggers.

I'm using Oracle SQL.

The limitations are:

  1. Only 1 class per course ID
  2. Students allowed max of 4 courses
  3. Courses allowed max of 25 students
  4. Each course taught be 1 instructor
  5. Each student only one major

Bolded 2 and 3 are where I'm having problems.

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

Upvotes: 2

Views: 179

Answers (2)

MT0
MT0

Reputation: 168232

You can try:

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

Testing:

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

Upvotes: 3

Turo
Turo

Reputation: 4914

an easy solution to make the constraints is:

Add integers STUDENT_LFDNR and COURSE_LFDNR to STU_CRSE

Add unique keys on (STUDENT_ID, STUDENT_LFDNR), (COURSE_ID, COURSE_LFDNR)

Add constarint ON STUDENT_LFDNR (>0 & <=4)

Add constraint ON COURSE_LFDNR (>0 & <=25)

But working with this will be painfull...

Upvotes: 0

Related Questions