当两个桌子包含相同的记录时，抛出例外

Question

我试图实现两个表的呼叫的ER模式：

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

我必须创建某种完整性约束，以防止两个表中存在的相同记录。也就是说，学生不得注册他们参加的课程。

我的假设是我需要使用触发器，因为一个简单的约束无法调用UDF（根据支票约束文档）。我还假设如果找到重复记录，我应该抛出例外。到目前为止，这是我到目前为止的：

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;

我问两个问题：

1. 我在正确的轨道上/这是最好的方法吗？

2. 如果是这样，那么我该如何检查重复记录？如果不是，那么实施这种约束的最佳方法是什么？

我无法将其合并到一个表中。

Answer 1

我对您的问题的回答：

1. 我认为您不在正确的道路上。
2. 您应该仅使用一个表格，并带有其他列，该列说该行是代表参加还是注册的课程学生。我将其命名为what_is_it（行＃4），然后检查约束（第11行），其中e表示“注册”，而r表示“注册”。如果您愿意，那也可能是外键。

类似的事情：

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>

这样做，其主要键将防止使用相同的[Student_ID，Course_crn]组合的两行。

---

为了学习目的（如您必须使用两个表），然后使用两个触发器 - 一个用于每个桌子。他们非常相似：

create or replace trigger trg_biu_enr
  before insert or update on enrolled_in
  for each row
declare
  l_cnt number;
begin
  select count(*)
    into l_cnt
    from registered_for
    where student_id = :new.student_id
      and course_crn = :new.course_crn;
  if l_cnt >= 1 then
     raise_application_error(-20001, 'Student has already REGISTERED that course');
  end if;
end;
/

create or replace trigger trg_biu_reg
  before insert or update on registered_for
  for each row
declare
  l_cnt number;
begin
  select count(*)
    into l_cnt
    from enrolled_in
    where student_id = :new.student_id
      and course_crn = :new.course_crn;
  if l_cnt >= 1 then
     raise_application_error(-20002, 'Student has already ENROLLED IN that course');
  end if;
end;
/

Answer 2

我无法将它们组合到一个表中。

创建仅用于执行约束的第三个表：

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

然后创建一个触发器来从父表中插入带有匹配行的子表中插入/删除/更新行，然后让primary/unique unique父表上的密钥执行约束。

---

或者，只需创建子表，然后使用实现的视图与Union一起使用。像：

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