search

DDieterich / DTGen

DTGEN Code Generation Tool for Relational Designers (See Wiki Branch)
http://dtgen.org
3 stars 0 forks source link

Need Temporal Continuity Test with "select for update" on FKs #2

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
What will the enhancement do?

  Prevent unintentional violation of integrity constraints due to a temporal shift in constraint enforcement.  For instance, a parent record could be created after child record in an EFF type table:

  insert into dept (dept_num, dept_name, eff_beg_dtm)
     values (10, 'Receiving', '29-SEP-2011');
  insert into emp (dept_num, emp_num, emp_name, eff_beg_dtm)
     values (10, 100, 'Duane', 28-SEP-2011');

  This would result in an ASOF view on 28-SEP-2011 that showed an employee (child record) without a required department (parent record).

How is this currently done or handled?

  The integrity constraint violation can/is happening without any warning.

What version of the product are you using? On what operating system?

  All

Please provide any additional information below.

  Possible design elements include:
  -) Establish FK Time Continuity from eff_beg_dtm to now on insert and update
  -) Establish FK Time Continuity from eff_end_dtm and aud_end_dtm to now on POP
  -) Establish FK Time Continuity for both parent and child foreign keys
  -) Throw an exception if the FK Time Continuity isn't confirmed

Original issue reported on code.google.com by Duane.Di...@gmail.com on 1 Sep 2011 at 2:52

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
This change will require both Parent and Child FK Temporal Continuity Checks.  
These checks must also be done in the POP function.  The placement of these 
checks would be:
 * Table_VIEW.ins
 * Table_VIEW.upd
 * Table_VIEW.del
 * Table_pop.at_server (no active, history)
 * Table_pop.at_server (active, no history)
 * Table_pop.at_server (active, history)

Original comment by Duane.Di...@gmail.com on 1 Sep 2011 at 5:54

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
Changed from enhancement to defect because it is included functionality that 
can cause data errors in an application.

Original comment by Duane.Di...@gmail.com on 4 Apr 2012 at 1:56

GoogleCodeExporter commented 9 years ago 
Need to address deleting records from NON table types that hold foreign key 
references from AUD or HIST table types.

Original comment by Duane.Di...@gmail.com on 18 Apr 2012 at 2:31

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
Need a "temporal_continuity" global parameter to enable/disable enforcement.

Original comment by Duane.Di...@gmail.com on 25 Aug 2012 at 2:44