Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There are two tables associated with each one to many in a field. In Table 2, there are fields that duplicate the value in Table 1.

Is there a cheaper way to ensure (guarantee) the correspondence fields except FK-type (id, x, y, z) in unique index in Table 1?

Creation of an index of type (id, x, y, z) and one FK (there certainly can be replaced by FK, but FK on one field looks easier) is very expensive because the tables are very large.

share|improve this question
1  
How is the information entered in each table? Does one process write to each table consecutively? Or does the second entry get created later? –  kevinsky Aug 28 '11 at 12:08
    
You are right, for my case probably will be important to how the data is added to these tables. This reporting table to export data, the data they are formed after processing algorithm by running a procedure in the package. As a result of the procedure at the same time the data comes in both tables. Later the data can be changed in both tables by user. –  sinys Aug 29 '11 at 7:06
    
drop the fields in table 2? –  Jack Douglas Aug 29 '11 at 8:32
    
these duplicated fields make a lot of queries very cheap –  sinys Aug 29 '11 at 9:40
    
@sinys have you tried other ways to make these queries cheap, eg Materialized Views or Result Caching? Or are you concerned about the overall size of the database? –  Jack Douglas Aug 29 '11 at 9:51
add comment

1 Answer

I don't know if there's a cheaper way to create a guarantee such as you are asking. But Foreign Key constraints are the tool for the job you are wanting to accomplish. It is probably better to use the right tool for the job in the long run. A hack solution might end up being a nightmare in administration and performance down the road.

Here is one possibility to get the constraint in with minimal downtime (logic only)

  • Create duplicate table structure of table 1 and table 2 (no data)
  • Create Unique Index on table1, and FK on table 2
  • Insert columns from table 1 into duplicate table 1, do the same for table 2
  • After you get all the info ported over, rename table1/table2 to old_table1/old_table2
  • Rename table1_new/table2_new to table1 and table2
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.