Refactoring «Add Lookup Table»

Create a lookup table for an existing column.

There are several reasons why you may want to apply Add Lookup Table:

  • Introduce referential integrity. You may want to introduce a referential integrity constraint on an existing column.
  • Provide code lookup. Many times you want to provide a defined list of codes in your database instead of having an enumeration in every application. The lookup table is often cached in memory.
  • Replace a column constraint. When you introduced the column, you added a column constraint to ensure that a small number of correct code values persisted. But, as your application(s) evolved, you needed to introduce more code values, until you got to the point where it was easier to maintain the values in a lookup table instead of updating the column constraint.
  • Provide detailed descriptions. In addition to defining the allowable codes, you may also want to store descriptive information about the codes. For example, in the State table, you may want to relate the code CA to California

To apply Add Lookup Table, you need to do the following:

  1. Select the table's column in the Object Explorer
  2. Right click on the selected column and then choose SQL Refactor Studio -> Add Lookup Tableā€¦ menu command.
  3. Enter a name for the new field in the table.

    Define the lookup table.

    Press the Next button to preview SQL Script.

  4. Press the Finish button to open the SQL code in a new query window.
  5. Modify generated code if you need and press the F5.