Throwing an exception when two tables contain the same record-CodePudding

The ER schema I am trying to implement calls for two tables:

create table enrolled_in (
    student_id number(6) not null,
    course_crn number(6) not null,
    constraint registers_for_pk primary key (student_id, course_crn),
    constraint registers_for_s_fk foreign key (student_id) references student(id),
    constraint registers_for_c_fk foreign key (course_crn) references course(crn)
);

create table registered_for (
    student_id number(6) not null,
    course_crn number(6) not null,
    constraint preregisters_for_pk primary key (student_id, course_crn),
    constraint preregisters_for_s_fk foreign key (student_id) references student(id),
    constraint preregisters_for_c_fk foreign key (course_crn) references course(crn)
);

I must create some sort of integrity constraint that prevents the same record from existing in both tables. That is, a student may not register for a course they are enrolled in.

My assumption is that I will need to use a trigger because a simple constraint cannot call a UDF (according to the CHECK constraints documentation). I am also assuming that I should throw an exception if a duplicate record is found. Here is what I have so far:

create or replace trigger reg_duplicate_check
    BEFORE insert on registers_for or enrolled_in
declare
    duplicate_registration exception;
begin
    select * into reg from registers_for;
    FOR r in reg
    LOOP
        -- check for a duplicate record in enrolled_in?
    END LOOP;
    exception
    when duplicate_registration then
        raise_application_error(-20004,'Duplicate record (enrolled_in and registers_for)');
end;

I am asking two questions:

Am I on the right track/is this the best way to do this?
If so, then how can I go about checking for the duplicate records? If not, what is the best way to implement such a constraint?

I am not able to combine these into one table.

CodePudding user response：

My answers to your questions:

I think you aren't on the right track.
You should use only one table, with additional column that says whether that row represents course student enrolled in or registered for. I named it what_is_it (line #4) and check constraint (line #11) where E represents "enrolled in", while R represents "registered for". That could also be a foreign key, if you want.

Something like this:

SQL> create table student_in_for
  2    (student_id    number(6),
  3     course_crn    number(6),
  4     what_is_it    varchar2(1),
  5     --
  6     constraint pk_stuinfor primary key (student_id, course_crn),
  7     constraint fk_stuinfor_stu foreign key (student_id)
  8       references student (id),
  9     constraint fk_stuinfor_cou foreign key (course_crn)
 10       references course (crn),
 11     constraint ch_stuinfor_what check (what_is_it in ('E', 'R'))
 12    );

Table created.

SQL>

Doing so, its primary key would prevent two rows with the same [student_id, course_crn] combination.

CodePudding user response：

I am not able to combine these into one table.

Create a third table that exists only to enforce the constraint:

CREATE TABLE enrolment_registration (
    student_id,
    course_crn,
    type CHAR(1),
    CONSTRAINT enrolment_registration__sct__pk PRIMARY KEY (student_id, course_crn, type),
    CONSTRAINT enrolment_registration__sc__u UNIQUE (student_id, course_crn),
    CONSTRAINT enrolment_registration__t__chk CHECK (type IN ('E', 'R')),
    CONSTRAINT enrolment_registration__s__fk
      FOREIGN KEY (student_id) REFERENCES student(id),
    CONSTRAINT enrolment_registration__c__fk
      FOREIGN KEY (course_crn) REFERENCES course(crn)
);

CREATE TABLE enrolled_in  (
    student_id,
    course_crn,
    type        CHAR(1) INVISIBLE GENERATED ALWAYS AS ('E'),
    CONSTRAINT enrolled_in_pk    PRIMARY KEY (student_id, course_crn),
    CONSTRAINT enrolled_in_sc_fk FOREIGN KEY (student_id, course_crn, type)
      REFERENCES enrolment_registration(student_id, course_crn, type)
);

CREATE TABLE registered_for  (
    student_id,
    course_crn,
    type        CHAR(1) INVISIBLE GENERATED ALWAYS AS ('R'),
    CONSTRAINT preregisters_for_pk    PRIMARY KEY (student_id, course_crn),
    CONSTRAINT preregisters_for_sc_fk FOREIGN KEY (student_id, course_crn, type)
      REFERENCES enrolment_registration(student_id, course_crn, type)
);

Then create a triggers to insert/delete/update rows from the child tables with matching rows in the parent table and let the PRIMARY/UNIQUE keys on the parent table enforce the constraints.

Or, just create the child tables and then use a MATERIALIZED VIEW to UNION them together. Something like:

CREATE TABLE enrolled_in  (
    student_id,
    course_crn,
    CONSTRAINT enrolled_in_pk    PRIMARY KEY (student_id, course_crn),
    CONSTRAINT enrolled_in_s_fk FOREIGN KEY (student_id) REFERENCES students(id),
    CONSTRAINT enrolled_in_c_fk FOREIGN KEY (course_crn) REFERENCES courses(crn)
);

CREATE TABLE registered_for  (
    student_id,
    course_crn,
    CONSTRAINT preregisters_for_pk    PRIMARY KEY (student_id, course_crn),
    CONSTRAINT preregisters_for_sc_fk FOREIGN KEY (student_id) REFERENCES students(id),
    CONSTRAINT preregisters_for_sc_fk FOREIGN KEY (course_crn) REFERENCES courses(crn)
);

CREATE MATERIALIZED VIEW enrolment_registration_mv
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
AS
SELECT student_id, course_crn, 'E' AS type FROM enrolled_in
UNION ALL
SELECT student_id, course_crn, 'R' AS type FROM registered_for;

ALTER VIEW enrolment_registration_mv
  ADD CONSTRAINT enrolment_registration_mv__sc__pk
  PRIMARY KEY (student_id, course_crn);