Key migration for physical data modeling

When you modify the properties of a key column or a relationship in a physical data model, the properties of any related foreign keys are also modified.
The following table describes actions on an object that cause key migration actions to occur. When key migration actions occur, the icons that decorate data objects in visual diagrams or in the Data Project Explorer change appropriately.
User action Key migration results
Add a column to a primary key

If the primary key takes part in a relationship, then the column is migrated to the child table as a foreign key column.

If the relationship is identifying, then the migrated column is also included as part of the primary key columns for the child table.

If the column is already part of a foreign key, the rest of the foreign key columns are already part of primary key, and the relationship is non-identifying, then the relationship is changed to identifying.

Remove a column from a primary key

If the primary key takes part in a relationship, then the column that had been migrated to the child table as a foreign key is removed from the foreign key columns list.

If the relationship is identifying, then the migrated column is also removed from the primary key columns list for the child table.

If the column is already part of a foreign key and the relationship is identifying, then the relationship is changed to non-identifying.

Reorder the columns of a primary key If the primary key takes part in a relationship, then the migrated columns in the foreign key are reordered to match the primary key.
Delete a primary key If the primary key participates in relationships, then the associated relationships and empty foreign keys are kept.
Modify the data type of a column If the column is part of a primary key and the primary key takes part in a relationship, then the data type of the migrated column on the child table is also changed.
Modify the length, precision, or scale of a column's data type If the column is part of a primary key and the primary key takes part in a relationship, then the data type length, precision, or scale of the migrated column on the child table is also changed.
Delete a column

If the column is part of primary key or unique key constraint and the primary key or unique key constraint participates in a relationship, then the migrated column on the child table is removed from the child foreign key column list.

If the relationship is identifying, then the migrated column on the child table is also removed from the child primary key column list.

Add a column to a unique key constraint

If the unique key constraint takes part in a relationship, then the column is migrated to the child table as a foreign key column.

If the relationship is identifying, then the migrated column is also included as part of the child table's unique key constraint columns.

If the column is already part of a foreign key, the rest of the foreign key columns are already part of unique key constraint, and the relationship is non-identifying, then the relationship is changed to identifying.

Reorder the columns of a unique key constraint No key migration occurs.
Remove a column from a unique key constraint

If the primary key takes part in a relationship, then the migrated column on the child table is removed from the child foreign key column list.

If the relationship is identifying, then the migrated column on the child table is removed from the child primary key column list.

Delete a unique key constraint If the unique key constraint participates in relationships, then the associated relationships and empty foreign keys are kept.
Add a column to a foreign key No key migration occurs.
Reorder the columns of a foreign key No key migration occurs.
Remove a column from a foreign key If the column is part of a primary key or key constraint and the primary key or key constraint participates in a relationship, then the migrated column on the child table is removed from the child foreign key column list. Otherwise, no key migration occurs.
Delete a foreign key The associated relationship is deleted from the child table.
Add a relationship

If the relationship is an identifying relationship, the migrated columns become part of the foreign key and the primary key of the child table.

If the relationship is a non-identifying relationship, the migrated columns become part of the foreign key of the child table.

Delete an identifying relationship The associated foreign key is deleted from the child table.
Delete a non-identifying relationship The associated foreign key is deleted from the child table.
Delete the parent table of a relationship All migrated key columns are removed from the foreign key of the child table.
Modify the type of a relationship

If the relationship is changed from identifying to non-identifying, then foreign key columns are removed from the primary key of the child table.

If the relationship is changed from non-identifying to identifying, then foreign key columns are added to the primary key of the child table.


Feedback