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 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.

Useful resources:

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

 

Related posts:

  1. SQL code refactoring – ways to improve database performance (part II) – Split table
  2. Rules of SQL formatting – Regular and delimited T-SQL identifiers
  3. Rules of SQL formatting – SQL naming conventions and capitalization rules
  4. How to format a SQL query to qualify object names explicitly
  5. SQL query basics – How to improve readability by formatting commas, spacing and alignment
February 27, 2015