Oracle - 如何根据属性记录对关系实施规则（简单示例）

Question

在学校系统中，我有两张表，一张称为“员工”，其中保存学校所有员工、教师、管理员、厨师和清洁工等的记录。然后我有第二张名为“课程”的表，其中包含与员工相关的外键以说明谁是课程领导者，现在我只想允许教师担任课程领导者，即厨师不能成为课程领导者，但我不确定如何在数据库级别上限制这一点。

注意：我在这里问了一个更复杂的错误问题 - Oracle 唯一约束 - 触发器检查新关系中的属性值

Answer 1

您可以在curses tabelle 上的插入或更新后触发器中检查此限制。

CREATE or replace TRIGGER check_leader
AFTER INSERT OR UPDATE ON  Course
FOR EACH ROW
declare
  v_type varchar2(30);
BEGIN
  select type into v_type from stuff where :NEW.leader_id = stuff.stuff_id;
  if v_type != 'teacher' then 
   RAISE_APPLICATION_ERROR(-20000, 'course leader must be teacher');
  end if;
end;
/

但你需要在员工桌上另一个触发器。
如果东西类型发生变化（从教师到清洁工），则必须检查诅咒表中的条目。

CREATE or replace TRIGGER check_courses
AFTER UPDATE ON  STUFF
FOR EACH ROW
declare
  v_num number;
BEGIN
  if :OLD.type = 'teacher' and :NEW.type != 'teacher' then
     select count(*) into v_num from curses where courses.leader_id = :NEW.stuff_id;
     if v_num > 0 then 
       RAISE_APPLICATION_ERROR(-20000, 'there are courses assigned ');
      end if;
  end if;
end;
/

Answer 2

在这种情况下，我会修改数据模型。我将采用通用表 Staff 并在其下添加每个 STAFF_TYPE 的表：CATERERS、TEACHERS、ADMIN 等。然后，在 COURSES 和 TEACHERS 之间强制使用外键就很简单了，无需触发器。

这是此类问题的标准解决方案。当您进一步调查这些要求时，您会发现厨师和清洁工也存在类似的外键问题。您也可能会发现教师具有而管理者所缺乏的某些属性。这就是为什么每种类型的单独表格很有用。同时，它们都有共同点。这就是为什么您需要一个 STAFF 超级类型的表。

---

当然，在提出这个答案时，我呼应@JeffreyKemp的 您上一个问题中的建议。好吧，正如 @RobVanWijk 借了我的，为什么不呢？ :)

Answer 3

您可以添加另一个名为 COURSE_LEADERS 的表来跟踪当前能够主持课程的每个人，然后建立从 COURSE 到 COURSE_LEADERS 的关系，如下所示以及从 COURSE_LEADER 到 STAFF 的关系。这样，厨师只能在 COURSE_LEADER 中主持一门课程，但如果这是一门关于烹饪的课程，也许可以……？ ;)

当然，这种方法意味着必须有某种方法可以在 course_leader 中添加/删除工作人员。如果您希望它是自动的，则可以有一个触发器，当添加新的STAFF（如果员工是教师）时，该触发器会向COURSE_LEADER插入一条记录 - 假设这是可能的在插入记录时确定员工是否是教师。

Answer 4

你可以像APC在另一个线程中所说的那样做。

这是一个示例：

SQL> create table staff
  2  ( id         number(10)   not null primary key
  3  , name       varchar2(10) not null
  4  , staff_type varchar2(10) not null check (staff_type in ('TEACHER','COOK','ADMIN','CLEANER'))
  5  , constraint staff_uk unique (id,staff_type)
  6  )
  7  /

Table created.

SQL> insert into staff values (1, 'ALAN', 'COOK')
  2  /

1 row created.

SQL> insert into staff values (2, 'BOB', 'TEACHER')
  2  /

1 row created.

SQL> create table course
  2  ( id         number(10)   not null primary key
  3  , name       varchar2(30) not null
  4  , staff_id   number(10)   not null
  5  , staff_type varchar2(10) default 'TEACHER' not null
  6  , constraint course_staff_fk
  7      foreign key (staff_id,staff_type)
  8      references staff (id,staff_type)
  9  )
 10  /

Table created.

SQL> insert into course (id,name,staff_id) values (1, 'Mathematics', 1)
  2  /
insert into course (id,name,staff_id) values (1, 'Mathematics', 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCHEMA.COURSE_STAFF_FK) violated - parent key not found


SQL> insert into course (id,name,staff_id) values (2, 'Physics', 2)
  2  /

1 row created.

问候，
抢。

Answer 5

我不认为在数据库级别执行此操作有什么意义，或者至少通过触发器等使您的生活变得复杂。

只需创建这些对象：

• 一个名为 STAFF 的表，有一个“type”字段（类型可以为 NULL 或“COURSE LEADER”）
• 一个名为 COURSE 的表，有一个指向 STAFF 的外键
• 一个名为 COURSE_LEADERS 的视图，即 select * from STAFF where type="COURSE LEADER"

然后，在处理课程和课程领导时，在应用程序中仅使用 COURSE 和 COURSE_LEADERS 对象。就这么简单。