I looking for the best way to check for inter-table constraints an step forward of foreing keys. For instance, to check if a date child record value is between a range date on two parent rows columns. For instance:
Parent table
ID DATE_MIN DATE_MAX
----- ---------- ----------
1 01/01/2009 01/03/2009
...
Child table
PARENT_ID DATE
---------- ----------
1 01/02/2009
1 01/12/2009 <--- HAVE TO FAIL!
...
I see two approaches:
Create materialized views on-commit as shown in this article (or other equivalent on other RDBMS).
Use stored-procedures and triggers.
Any other approach? Which is the best option?
UPDATE: The motivation of this question is not about "putting the constraints on database or on application". I think this is a tired question and anyone does the way she loves. And, I'm sorry for detractors, I'm developing with constraints on database. From here, the question is "which is the best option to manage inter-table constraints on database?". I'm added "inside database" on the question title.
UPDATE 2: Some one added the "oracle" tag. Of course materialized views are oracle-tools but I'm interested on any option regardless it's on oracle or others RDBMSs.
