2

SQL has well understood ways to do one-to-one, one-to-many, and many-to-many relationships. But what about one-to-n, where n is a known natural number that isn't expected to change. (For example, let's say every weekly pay period statement is expected to have seven daily entries.)

Is there any way to maintain a constraint like this at the database level?

The best I can think of would be to have n foreign keys on the one, so in our example the weekly statement table would have columns for day_0, day_1, day_2, etc, which seems uncommon and possibly not 1NF.

This question is inspired by a recent meme of "making impossible states impossible" in the Elm programming language community.

  • Is there any way to maintain a constraint like this at the database level? No. Such constraint do not allow to create parent record - while creating no child records, so constraint fails. But you can create constraint "not more than N". – Akina Oct 8 '19 at 12:05
  • 1
    @normal_form is the question about a specific DBMS or SQL in general? – ypercubeᵀᴹ Oct 8 '19 at 12:13
  • 1
    In theory, this is was assertions are for in SQL - but unfortunately no database implements them. To some extent you could probably get away with triggers, if you really need to enforce that - but those wouldn't be safe for concurrent inserts. In Oracle there would be an ugly way to do that with a materialized view that does a refresh on commit – a_horse_with_no_name Oct 8 '19 at 12:21
  • General implementation. Parent table have AFTER INSERT trigger which creates N empty records in child table. Client cannot create or delete child records - update only. – Akina Oct 8 '19 at 12:24
  • 2
    And it is doable (I think) with DDL alone but is rather complicated without assertions. – ypercubeᵀᴹ Oct 8 '19 at 12:27
1

Another level of indirection to the rescue!

Add a Week table, containing the date of the first day of the week and whatever else seems sensible (week number, Year, etc), with one row per week. The Paycheck table has an FK pointing to the Week table.

Add a Day table. This has one row for each day, and contains the Date and an FK to the relevant Week table. It might also have indicators that this is a weekend or national holiday, and so different pay-rates might apply.

Add a table PaycheckDay. This has FK's for the Paycheck row and the appropriate Day row. These two FK's should also be a unique non-clustered index. Pay details for each day are stored on this row. For extra security there could also be constraint that the Day belongs to the same Week that the Paycheck does (also this might be enforced by a Trigger rather than a constraint depending on physical database type).

|improve this answer|||||
  • But the PaycheckDay table and indexes don't keep you from adding an eighth day to a paycheck, right? Have I missed something? – normal_form Oct 8 '19 at 18:22
  • @normal_form - you can't have 8 unique days for the same week. But, I don't see how this is enforcing "at least 7 rows" either. – Michael Kutz Oct 8 '19 at 23:05
  • I imagined the Day and Week tables as static, extended every now and then (e.g. every 2 years) by script. And yes, the script can create weeks with no days or with 500 days, but if your processes are that broken you have larger problems. Not everything can - or possibly should be - enforced in the database. – simon at rcl Oct 9 '19 at 9:34
1

Is there any way to maintain a constraint like this at the database level?

Yes and No.

Yes This is done via ASSERTION

No ASSERTIONS are not (currently) supported by any of the major RDBMS. Some minor DBs claim to support them.

Workarounds There are various methods to hack "what is currently available" into something that enforces such a constraint.

Base Tables for Workaround "hacks"

These tables will used by the other examples. NO GRANTS HAVE BEEN GIVEN!

Oracle syntax

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

Method 1 - Fix the data model

This data model seems to be a "bad data model". The recommendation is to change it.

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;

If you need to, create an INSTEAD OF trigger on the payroll_week. In order to allow DML against the VIEW:

grant insert,update,delete on payroll_week to some_role;

Method 2 - ASSERTION

This method was mentioned by a_horse_with_no_name in the comments.

Reminder, no major RDBMS supports ASSERTIONS -- yet.

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;

Method 3 - complex DDL

This method was mentioned by ypercube(tm) in the comments.

The payroll_parent_dy* constraints are DEFERRED constraints. This allows you to add a parent and then the children. Validation is done at COMMIT;

Syntax is for 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;

Method 4 - after INSERT trigger

This method was mentioned by Akina in the comments.

NOTE "bad data" can still get into the tables (by the owner of the table) since there is no constraint/assertions

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;

Method 5 - Materialized View

This method was mentiod by a_horse_with_no_name in the comments.

notes:

  • REFRESH FAST ON COMMIT will modify the data in the MV incrementally but you lose the ability to test if there is "at least 1 ROW"
  • REFRESH COMPLETE ON COMMIT will rebuild the entire MV table but you can use a LEFT OUTER JOIN to ensure that you have "at least 1 row"

Oracle syntax

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

Method 5b - maintain the N with triggers

Instead of managing the value of N via Materialized View, you use a trigger to manage the data in the table.

NOTE "bad data" can still get into the tables if there ways to by-pass the trigger. (eg Oracle Direct Loads will by-pass triggers)

Method 5c - too complex to be considered as a viable solution

Oracle allows you to create your own INDEX. Oracle calls these Domain Indexes. The most famous Domain Indexes are Text and Spatial (GIS Index for other RDBMS).

This method moves all the code from 5b into a more automagical/can_apply_to_any_table capability. It has the upside that "bad data" can't get in unless the Index is disabled.

The downside is that this method is very tedious/code intensive to create a solution for a simple business requirement, especially for a 1-off task.

The underlying CHECK constraint will need to be a DEFERRABLE Constraint.

Method ??

There are probably other methods.

Feel free to add yours between 5b and this one. Make sure you note which RDBMS you are using.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.