After explaining basic principles for replacing one-to-many relationships with associative tables in Part 1 of the article, this sequel will show 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, under the Other refactors sub-menu integrated in SQL Server Management Studio and Visual Studio:
After that, the Replace one-to-many relationship window will be displayed:
In this window 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.
Creating a relationship between tables
Sample: Retrieve with one-to-many relationship
February 27, 2015