How to make a duplicate field cheap?

Question

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.

Answer 1

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