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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SELECT LlaveForanea =o.name, Esquema =SCHEMA_NAME(t1.schema_id), Tabla =t1.name, Columna =c1.name, Esquema_Ref =SCHEMA_NAME(t2.schema_id), Tabla_Ref =t2.name, Columna_Ref =c2.name, ScriptDrop = 'ALTER TABLE ' + SCHEMA_NAME(t1.schema_id) + '.' + t1.name + ' DROP CONSTRAINT ' + o.name, ScriptCreate = 'ALTER TABLE ' + SCHEMA_NAME(t1.schema_id) + '.' + t1.name + ' ADD CONSTRAINT ' + o.name+' FOREIGN KEY ('+c1.name+')' + ' REFERENCES ' + SCHEMA_NAME(t2.schema_id)+'.'+t2.name + '('+c2.name+')' FROM sys.foreign_keys o INNER JOIN sys.foreign_key_columns fk ON o.object_id = fk.constraint_object_id INNER JOIN sys.tables t1 ON t1.object_id = fk.parent_object_id INNER JOIN sys.columns c1 ON c1.column_id = parent_column_id AND c1.object_id = t1.object_id INNER JOIN sys.tables t2 ON t2.object_id = fk.referenced_object_id INNER JOIN sys.columns c2 ON c2.column_id = referenced_column_id AND c2.object_id = t2.object_id --FILTRO OPCIONAL, si se desea identificar FKs de una tabla en particular -- o que referencian a una tabla en particular WHERE SCHEMA_NAME(t2.schema_id)='Person' AND t2.name = 'Person'; GO |
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
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
|
1 2 3 |
ALTER TABLE Person.BusinessEntityContact DROP CONSTRAINT FK_BusinessEntityContact_Person_PersonID; GO |
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
|
1 2 3 4 |
ALTER TABLE Person.BusinessEntityContact ADD CONSTRAINT FK_BusinessEntityContact_Person_PersonID FOREIGN KEY (PersonID) REFERENCES Person.Person(BusinessEntityID); GO |
Con esto, hemos cumplido el objetivo de:
-
Identificar las llaves foráneas de una base de datos, de una tabla en particular o de una tabla referenciada en particular
-
Obtener los scripts para eliminar las llaves foráneas
-
Obtener los scripts para recrear las llaves foráneas
Hasta la próxima!
Buenos días excelente aporte aporte , gracias