Automatic Reuse and Migration of Columns

When you create a reference, PowerDesigner can automatically reuse an appropriate existing column in the child table as the foreign key column and migrate the primary key column in the parent table to create a foreign key column in the child table.

1. Select Tools > Model Options to open the Model Options dialog box and select the Reference sub-category in the left-hand Category pane.
2. Select the following options as appropriate:

   Option	Function
   Auto-reuse columns	Enables the reuse of columns in a child table as foreign key columns when creating references if the following conditions are satisfied: The child column has the same code as the migrating primary key column. The child column is not already a foreign key column. If you want to reuse a child table column that is already a foreign key column, you must do this manually from the Joins tab of the reference property sheet. Data types are compatible.
   Auto-migrate columns	Enables the automatic migration of primary key columns from the parent table as foreign key columns to the child table when creating references. Select the following column property checkboxes as appropriate to specify parent column properties to migrate: Domains (see Domains (CDM/LDM/PDM)) Check (see Setting Data Profiling Constraints). Rules (see Business Rules (CDM/LDM/PDM)). Last position - Adds migrated columns at the end of the table column list. If this option is not selected, migrated columns are inserted between key columns and other columns which implies that a child table must be dropped and recreated each time you add a reference and modify an existing database. Note: During intermodel generation, whether or not this option is selected, any selected column property is migrated from the PK to the FK.
   Default link on creation	Specifies whether reference joins are automatically created: Primary key – Automatically create joins between the parent table primary key and a child table foreign key. If the Auto-migrate columns option is not selected then you must manually specify foreign key columns on the reference Joins tab. User-defined – Does not create joins. You must manually select columns on the reference Joins tab.

3. Click OK to close the dialog and return to your model.

The following table shows the results of migrating and reusing primary key columns to a child table that contains a matching child table column, and where that child table column is already a foreign key column for another table:

Options Selected	Matching Child Table Column Exists	Matching Child Table Column Is Already a FK Column
[Original tables before migration]	The child table contains a matching column for one of the primary key columns:	The child table contains a matching column that is already a foreign key column for another table:
[default] Auto-reuse and Auto-migrate	Col_1 is reused and Col_2 is created:	T1_Col_1 and Col_2 are created:
Auto-migrate only	T1_Col_1 and Col_2 are created:	T1_Col_1 and Col_2 are created:
Auto-reuse only	Col_1 is reused but Col_2 is not created:	No columns are reused or created:
Neither	No column is reused or created	No columns are reused or created:

Note:

• By default, only the properties of the primary key column are migrated to the foreign key. If the primary key column is attached to a domain, the domain will not be migrated to the new foreign key column unless the Enforce non-divergence model option is selected (see Controlling Non-Divergence from a Domain).
• If you have selected the Auto-migrate columns model option and you modify a reference attach point then you will migrate primary keys in the parent table to foreign keys in the child table, delete unused foreign key columns, and modify the reference join. If you delete the parent primary key column then you will delete the corresponding foreign key and reference join.

For more information about other reference model options, see Reference Model Options.