I agree partially with the above response from @Justin Cave. If the data has a required relation such as begin_date < end_date, this should be enforced at the source.
A table constraint is a way to do this. I will borrow the first recommendation, which will prevent any illogical corruption of your data set:
ALTER TABLE bravo_unit_timetable
ADD CONSTRAINT bravo_unit_timetable_ck
CHECK( end_period_date > begin_period_date or
end_period_date is null );
ANY DML applied to the table in this case will be filtered and blocked if this constraint is not followed.
Applying a table-level check constraint was a partial solution as the question was tagged oracle-apex. The table is a part of an Apex environment which also includes application-level elements.
After building a form in Apex to handle data entry into this table, the interpreted DML from the Apex form is not very friendly:
The top of the screenshot shows the result of an invalid date pairing of date values in the input form:
The table-level catches the invalid data relation and throws the check constraint error created originally with the source table.
But what IS: BRAVO_UNIT_TIMETABLE_CK1? What IS an ORA-02290? This is a dead end for your end users if you've developed an APEX application for distribution to anyone without SQL level access to the data table in question. Deployed in this condition, it's just another distraction for developers or tech staff who will end up explaining this over a support call.
Set up a Page-level validation which also can catch this requirement for the date range values:
Within the validation rule definition, borrow the same logic used in the check constraint but adapt it to implement references to the PAGE FORM ITEMS instead:
There's lots of ways to configure the validation rule, but the one that worked (from this example) was:
Validation Type: PL/SQL Expression
Validation Expression 1:
CASE WHEN :P2_END_PERIOD_DATE > :P2_BEGIN_PERIOD_DATE OR
:P2_END_PERIOD_DATE is NULL THEN TRUE
ELSE FALSE
END;
Where the referenced variables are specific to my form design. (Modify as needed for your own design).
Attempting the same kind of invalid data entry with:
Here is what the Apex application returns when a validation rule catches the input prior to executing an actual DML command:
Result of an Invalid Form Entry Caught with an APEX Validation
The error message returned now looks like something an End User can resolve on their own. This now resembles a complete solution for the OP, as it involves both the Oracle database back-end (important) and also the front-end (user interface element).
A few fleeting thoughts on the topic of this post; it opens up a great discussion on a participant in software development whom tech people sometimes ignore or regard in disgust: the end user.
The Developer's Friend, The End User
The End User is sometimes obvious, but they are throughout many systems. It's important to provide feedback or a mechanism of self-service to enable them to understand when a mistake is made and immediately how to remedy it. If the problem is very difficult to solve or is unanticipated by its caretakers, relevant and up-dated contact information should be left in plain view for further assistance.
Yes, but aren't TWO different validation rules redundant?
The validation rules serve two different purposes in protecting the quality of the data:
The database is actually serving an "integrity" constraint by maintaining a rule from the system's requirements (a system can be any number of applications and data inputs/outputs surrounding it). In this case, the rule is simple:
In this system people do not travel backwards in time.
Putting the validation on the database only yielded a cryptic response. Input data was blocked, serving the data quality purpose. The requirement of the Apex application, alternatively had failed and was left with a dependency on support outside of the application for further explanation.
Onward.
Assuming that both columns are of type date, the check constraint would be
ALTER TABLE name_of_table
ADD CONSTRAINT name_of_constraint
CHECK( end_date > start_date );
Note that this does not account for the potential for having null values in either column. If end_date may be null, you'd probably want
ALTER TABLE name_of_table
ADD CONSTRAINT name_of_constraint
CHECK( end_date > start_date or
end_date is null );
