在SQL中使用1-to-精确的-n

Question

SQL已经很好地理解了实现一对一、一对多和多对多关系的方法。但是，1对n呢，其中n是一个已知的自然数，预计不会改变。(例如，假设每周薪资期报表都有七条每日条目。)

在数据库级别有任何方法来维护这样的约束吗？

我能想到的最好是在这个表上有n个外键，所以在我们的示例中，每周语句表将包含用于day_0、day_1、day_2等的列，这似乎不常见，而且可能不是1NF。

这个问题的灵感来源于最近榆树编程语言社区中的“使不可能的状态不可能”的模因。

Answer 1

另一个水平的间接救援！

添加一个周表，其中包含一周的第一天的日期以及其他看起来合理的日期(周数、年份等)，每周一行。Paycheck表有一个指向Week表的FK。

添加一张日表。它每天都有一行，并包含相关周表的日期和FK。它也可能有迹象表明，这是一个周末或国家假日，因此，不同的工资标准可能适用。

添加一个表PaycheckDay。这有FK的支付宝行和适当的日行。这两个FK也应该是一个独特的非聚集索引.每天的薪资详细信息存储在该行中。为了获得额外的安全性，还可能会有限制，即Day属于Paycheck所做的同一周(这也可能由触发器执行，而不是依赖于物理数据库类型的约束)。

Answer 2

在数据库级别有任何方法来维护这样的约束吗？

是和不。

是的，这是通过ASSERTION完成的

没有一个ASSERTIONS (目前)不受任何主要关系数据库管理系统的支持。一些小的DBs声称支持他们。

解决方法有多种方法，可以将“当前可用的内容”破解为强制执行这种约束的方法。

用于解决“黑客”问题的

基本表

其他示例将使用这些表。没有拨款！

Oracle语法

create table payroll_parent (
    parent_id      int
    ,start_of_week date
    ,constraint payroll_parent_pk primary key (parent_id)
    ,constraint payroll_parent_ck1 check ( to_char(start_of_week,'fmDay') = 'Sunday' and start_of_week = trunc(start_of_week) )
);

create table payroll_child (
    parent_id   int        --pk
    ,day_of_week    number(1)  --pk
    ,wages_earned   number(8,2) default 0 not null
    ,constraint payroll_child_pk primary key (parent_id, day_of_week)
        ,constraint payroll_child_fk1 foreign key (parent_id) references payroll_parent(parent_id) on delete cascade
    ,constraint payroll_child_ck1 check (day_of_week between 0 and 6)
);

comment on column payroll_child.day_of_week is '0 = Sunday';
comment on column payroll_child.wages_earned is 'Dollars earned in USD';

方法1-修正数据模型

这个数据模型似乎是一个“糟糕的数据模型”。建议是改变它。

drop table payroll_child;
drop table payroll_parent;


create table payroll_earnings (
    employee_id   int not null
    ,day_of_wages  date not null
    ,wages_earned  number(8,2) default 0 not null
    ,day_of_week  as (to_number(to_char(day_of_wages,'WI')))
    ,start_of_week as ( case
                            when to_char(day_of_wages,'fmDAY') = 'SUNDAY' then day_of_wages
                            else next_day(day_of_wages,'SUNDAY') - 7
                        end
                        )
    ,constraint pe_pk primary key (employee_id,day_of_wages)
    ,constraint pe_ck1 check (day_of_wages = trunc(day_of_wages) )
);

create or replace view payroll_week
as
select employee_id
    ,start_of_week
    ,nvl("0",0) as SUNDAY
    ,nvl("1",0) as MONDAY
    ,nvl("2",0) as TUESDAY
    ,nvl("3",0) as WEDNSDAY
    ,nvl("4",0) as THURSDAY
    ,nvl("5",0) as FRIDAY
    ,nvl("6",0) as SATURDAY
from payroll_earnings
    pivot (
      sum(wages_earned)
      for day_of_week in ( 0,1,2,3,4,5,6 )
    )
);

grant select, insert, update,delete on payroll_earnings to some_role;
grant select on payroll_week to some_role;

如果需要，在payroll_week上创建一个替代触发器。为了允许DML针对视图：

grant insert,update,delete on payroll_week to some_role;

方法2-断言

a_horse_with_no_name在注释中提到了这种方法。

提醒您，目前还没有主要的关系数据库支持ASSERTIONS。

create assertion payroll_assert_7_days check (
    7 = all(
            select count(b.parent_id)
            from payroll_parent a
                left outer join payroll_child b on a.parent_id=b.parent_id
            group by a.parent_id
        )

)
deferrable initially deferred;

grant select,insert,update,delete on payroll_parent to some_role;
grant select,insert,update,delete on payroll_child to some_role;

方法3-复DDL

ypercube(tm)在注释中提到了这种方法。

payroll_parent_dy*约束是延迟约束。这允许您添加一个父级，然后添加子级。验证在COMMIT进行；

语法是用于Oracle的

alter table payroll_parent add (
    day_0 as ( cast(0 as number(1)) )
    ,day_1 as ( cast(1 as number(1)) )
    ,day_2 as ( cast(2 as number(1)) )
    ,day_3 as ( cast(3 as number(1)) )
    ,day_4 as ( cast(4 as number(1)) )
    ,day_5 as ( cast(5 as number(1)) )
    ,day_6 as ( cast(6 as number(1)) )
);

alter table payroll_parent
    add constraint payroll_parent_dy0 foreign key (parent_id,day_0) references payroll_parent(parent_id, day_of_week)
    deferrable initially deferred;
alter table payroll_parent
    add constraint payroll_parent_dy1 foreign key (parent_id,day_1) references payroll_parent(parent_id, day_of_week)
    deferrable initially deferred;
alter table payroll_parent
    add constraint payroll_parent_dy2 foreign key (parent_id,day_2) references payroll_parent(parent_id, day_of_week)
    deferrable initially deferred;
alter table payroll_parent
    add constraint payroll_parent_dy3 foreign key (parent_id,day_3) references payroll_parent(parent_id, day_of_week)
    deferrable initially deferred;
alter table payroll_parent
    add constraint payroll_parent_dy4 foreign key (parent_id,day_4) references payroll_parent(parent_id, day_of_week)
    deferrable initially deferred;
alter table payroll_parent
    add constraint payroll_parent_dy5 foreign key (parent_id,day_5) references payroll_parent(parent_id, day_of_week)
    deferrable initially deferred;
alter table payroll_parent
    add constraint payroll_parent_dy6 foreign key (parent_id,day_6) references payroll_parent(parent_id, day_of_week)
    deferrable initially deferred;


grant select,insert,update,delete on payroll_parent to some_role;
grant select,insert,update,delete on payroll_child to some_role;

方法4-插入触发器后的

该方法在评论中提到。

注意“坏数据”仍然可以进入表(由表的所有者)，因为没有约束/断言。

create or replace
trigger payroll_parent_trg
after insert
on payroll_parent
for each row
begin
    -- there are better ways
    for i in 0 .. 6
    loop
        insert into parent_child(parent_id,day_of_week,wages_earned)
        values (:new.parent_id, i );
    end loop;
end;
/

grant select,insert,update,delete on payroll_parent to some_role;
grant select,update on payroll_child to some_role;

方法5-物化视图

a_horse_with_no_name在评论中提到了这一方法。

注：

• 提交时快速刷新将逐步修改MV中的数据，但您将失去测试“至少1行”的能力。
• 在提交时刷新完成将重新构建整个MV表，但可以使用左外部联接来确保“至少有1行”。

Oracle语法

create materialized view log on payroll_parent with rowid (parent_id) including new values;
create materialized view log on payroll_child with rowid (parent_id) including new values;

create materialized view payroll_count_mv
 build immediate
 refresh fast
 on commit
 disable query rewrite
as
select a.parent_id, count(a.parent_id) N
from payroll_parent a
    join payroll_child b on a.parent_id=b.parent_id
group by a.parent_id
;

alter table payroll_count_mv
    add constraint there_can_be_only_7 check (nvl(N,0) = 7 );

方法5b -使用触发器

维护N

不是通过物化视图管理N的值，而是使用触发器来管理表中的数据。

注意，如果有方法通过触发器，“坏数据”仍然可以进入表中。(如Oracle直接加载将通过触发器)

方法5c -太复杂，不能被认为是可行的解决方案

Oracle允许您创建自己的索引。Oracle调用这些域索引。最著名的领域索引是文本和空间(其他关系数据库的GIS索引)。

此方法将所有代码从5b移动到更自动化的/can_apply_to_any_table功能中。它的好处是，除非索引被禁用，否则“坏数据”无法进入。

缺点是这种方法非常繁琐/代码密集，难以为简单的业务需求创建解决方案，特别是对于一次性任务。

基本的检查约束需要是可推迟的约束。

方法??

可能还有其他方法。

你可以在5b和这个之间加上你的。确保您注意到您正在使用的是哪个RDBMS。