Say I have the following schema:
BUDGET_YEAR_TARGET
INTEGER ID
BUDGET_DATE_TARGET
INTEGER ID
INTEGER BUDGET_YEAR_TARGET_ID--Foreign key
DATE DATE
How can I ensure that any records of BUDGET_DATE_TARGET
that share a BUDGET_YEAR_TARGET_ID
will have DATE
s under the same Fiscal Year (with Oct 1 as new year day)?
For example, this would be valid:
ID BUDGET_YEAR_TARGET_ID DATE
1 1 Jan 1, 2020
2 1 Jul 1, 2020
3 2 Jan 1, 2020
4 2 Aug 1, 2020
5 3 Nov 1, 2020
If I were to insert a new record with a DATE
of Dec 1, 2020, I'd like it to succeed if the BUDGET_YEAR_TARGET_ID
is 3 but fail if it is 1 or 2.
How can I achieve that?
Best Answer
A before insert trigger might be an option...
However, a "dates table" aka "calendar table" is much more flexible, regardless of the DBMS you are using.
Basically, it's a pre-generated table of dates from say 1900-01-01 to 2200-12-31. In addition to various date columsn, it would contain various flag columns (weekday, weekend, holiday) and other helpful columns (Fiscal year, Quarter).
A bit of searching will turn up lots of articles about using them.
For Db2 for i specifically, if you have the Web Query product installed, IBM provides a routine (LOAD_DATE_CONVERSION_TABLE) to generate one (QWQCENT.DATE_CONV) for you. See chapter 5 of the Redbook - IBM DB2 Web Query for i
For your case, you could define a unique key constraint on your dates table over (date, fiscal_year) and then use that as a FK constraint in your BUDGET_DATE_TARGET.
However, you may find you don't need BUDGET_DATE_TARGET or BUDGET_YEAR_TARGET at all once you've got the dates table.