Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 16 Jun 2006 @ 11:09:03 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: How to validate the invalid references created
 
From:   Waters, Gerald

  What should I do now to validate the invalid references?? Does it mean that when we archive a database (which contains tables with foreign keys) and later restoring the database, the tables which has foreign keys are no longer reusable.????  


I found the only way to stay sane while using RI on the Teradata was to never put the integrity references into the CREATE statements.

Instead, you can put them into separate named constraints. This then lets you use a script to apply all the constraints and a similar one to drop them all.

e.g.

     ALTER TABLE Table_A ADD CONSTRAINT Constraint_N FOREIGN KEY ( FKeyCol )

     REFERENCES WITH CHECK OPTION Table_B ( PKeyCol ) ;

and the drops are then simply:

     ALTER TABLE Table_A DROP CONSTRAINT Constraint_N ;

I originally did this just to let me replace tables with the constraints

turned off but I later realised that I no longer had to be as careful about the order in which I repopulated the tables.

Eventually I came to the conclusion that the integrity constraints, while useful during the development phase of my project, were mainly constraining performance. Now I apply the constraints when and how I see fit.

I can do this because moving them from CREATE to ALTER has shifted it from being a design-time decision to a run/manage-time decision.

Even if you want to run the constraints 100% of the time it might be worth turning them off this way just for the archive and restore.

The hardest part was finding a manageable naming convention for all the named constraints. If I do this again I think I'll use numbered dummy names, keep them in a table and generate the ADD and DROP scripts, both generally and selectively.



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023