Llaves foraneas que hacen referencia a una tabla

Esta semana se me presentó un reto, como parte del diseño e implementación del particionamiento de unas tablas en un cliente. Se me cruzó por el camino la necesidad de recrear una llave primaria. Si dicha llave primaria está referenciada por una o varias llaves foráneas, SQL Server activa los mecanismos de integridad referencial e impide que se elimine dicha llave primaria. (Eso es el comportamiento normal, no es un error).

El problema aquí está en que necesito recrear esa llave primaria para particionar la tabla, por lo tanto requiero identificar todas las llaves foráneas que hacen referencia a esa llave primaria, eliminarlas para poder recrear dicha llave primaria, y luego recrear todas las llaves foráneas para dejar la base de datos en su estado original.

Esta situación planteó el reto de desarrollar un query que no solo identificara las llaves foráneas, si no que me permita armar el script, tanto de eliminación (DROP) cómo de creación (CREATE).

Lo primero: identificar las vistas del sistema que tengan la información necesaria para cumplir el objetivo:

  • sys.foreign_keys.- Vista que muestra todos los objetos de una base de datos, como tablas, vistas, procedimientos, funciones y, claro está, llaves primarias y foraneas
  • sys.foreign_key_columns.- Vista que muestra las columnas de las llaves foráneas
  • sys.tables.- Vista que contiene las tablas de la base de datos (solo las tablas de usuario, no las del sistema)
  • sys.columns.- Vista que contiene las columnas de las tablas de la base de datos

Una vez identificadas las fuentes de la información, toca armar el query:

Si ejecutamos el query, obtendremos el siguiente resultado (Estamos usando la base de datos AdventureWorks, tabla Person.Person)

NOTA: . El script ha sido probado desde  la versión SQL Server 2012 en adelante

image

Vamos a explicar las columnas que retorna el query:

  • LlaveForanea.- nombre de la llave foránea.
  • Esquema.- Nombre del esquema al que pertenece la tabla poseedora de la llave foránea
  • Tabla.- Nombre de la tabla poseedora de la llave foránea
  • Columna.- Nombre de la columna de la llave foránea
  • Esquema_Ref.- Nombre del esquema al que pertenece la tabla referenciada
  • Tabla_Ref.- Nombre de la tabla referenciada
  • Columna_Ref.- Nombre de la columna referenciada
  • ScriptDrop.- Script de eliminación de la llave foránea
  • ScriptCreate.- Script de creación de la llave foránea

El query puede mostrar todas las llaves foráneas de la base de datos o se puede agregar un filtro por la tabla poseedora de la llave o por la tabla referenciada. El objetivo es principalmente obtener los scripts. En este caso, sigamos que quiero hacer cambios o recrear la llave primaria de la tabla Person.Person. Esto es lo que hay que hacer:

1. Ejecutar la consulta filtrando la tabla referenciada Person.Person

2. Utilizar los scripts de DROP para eliminar las llaves y así poder hacer cambios o recrear la llave primaria de Person.Person

3. Una vez hechos los cambios en Person.Person, utilizar los scripts de CREATE para recrear las llaves foráneas y dejar la base de datos en su estado original, con los objetos de integridad referencial intactos

Con esto, hemos cumplido el objetivo de:

  1. Identificar las llaves foráneas de una base de datos, de una tabla en particular o de una tabla referenciada en particular
  2. Obtener los scripts para eliminar las llaves foráneas
  3. Obtener los scripts para recrear las llaves foráneas

Hasta la próxima!

Deja un comentario

Tu dirección de correo electrónico no será publicada.