Archives of the TeradataForum

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

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

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>>