sql - 基于父记录约束子记录

Question

在时间表数据模型中，假设我有以下父表:

CREATE TABLE EmployeeInRole (
    employeeInRoleId PRIMARY KEY,
    employeeId,
    roleId,
    rate,
    effectiveFrom DATE, --from when can this employee assume this role
    effectiveTo DATE
);

以及以下子表:

CREATE TABLE TimesheetEntry (
    startTime DATETIME,
    endTime DATETIME,
    employeeInRoleId,
    CONSTRAINT fk FOREIGN KEY (employeeInRoleId) REFERENCES EmployeeInRole (employeeInRoleId)
);

当我插入 TimesheetEntry 时，我想确保时间段落在父记录的 effectiveFrom/To 的范围内。

是否可以在不使用触发器的情况下将此约束构建到 DDL 中，或者我是否必须通过触发器或在应用程序级别维护此约束？

Answer 1

(这里仅提供有关 Oracle 的一些信息)

在 Oracle 中不可能使用清晰的 DDL，但你可以这样做:

create table t1 (id number primary key, date_from date, date_to date);
create table t2 (id number primary key, date_from date, date_to date, parent_id number references t1(id));
create view v as 
select t2.* from t2 
where exists (select 1 from t1 where t1.id = t2.parent_id 
  and t2.date_from between t1.date_from and t1.date_to
  and t2.date_to between t1.date_from and t1.date_to)
with check option constraint chk_v;

insert into t1 values (1, sysdate - 5, sysdate); -- OK
insert into v values (1, sysdate - 4, sysdate - 3, 1); -- OK
insert into v values (1, sysdate - 6, sysdate - 3, 1); -- ERROR (WITH CHECK OPTION where-clause violation)

V 是使用 CHECK OPTION 创建的可更新 View