Replacing one-to-many relationships with association tables automatically – Part 2

After explaining basic principles for replacing one-to-many relationships with associative tables in Part 1 of the article, this sequel will show you how to easily accomplish that via the Replace one-to-many relationship feature.

The Replace one-to-many relationship command is located under the ApexSQL -> ApexSQL Refactor submenu integrated in SQL Server Management Studio and Visual Studio:

The feature can also be accessed via the context menu, as well. Right-click the target table and select the Replace one-to-many relationship command from the ApexSQL Refactor menu:

After that, the Replace one-to-many relationship dialog will be displayed:

In this dialog the dependent table, the relationship and a foreign key reflecting the one-to-many relationship that will be replaced, and the name for the associative table need to be specified.

The Preview option will show the SQL script to be executed; it will be displayed under the Generated script tab:

The Warnings tab displays potential problems that can occur when using the Replacing one-to-many relationship refactor:

The Sequence tab contains summarized operations and their execution order. The execution sequence is generated in the specific manner that prevents dependency problems:

Select the Dependencies tab to preview dependent objects, which will be updated automatically to reflect the change:

Replacing one-to-many relationships is easy with ApexSQL Refactor.

Useful resources

Table relationships
Creating a relationship between tables
Sample: Retrieve with one-to-many relationship

February 27, 2015