De vez en cuando, como formador y consultor, tengo que justificar a nuestros clientes los beneficios que supone usar Integridad Referencial Declarativa (Claves Ajenas). Generalmente tengo comentarios del siguiente tipo:

  • No defino claves ajenas porque es más fácil hacer llenados de tablas.
  • Prefiero validar la integridad en la capa de acceso a datos que en la base de datos.
  • los accesos a la base de datos se realizan mediante PAs que yo controlo, y no se pueden introducir inconsistencias.
  • No lo implementamos porque el modelo relacional varia con mucha frecuencia, y resulta complejo hacer cambios.

hay muchos más casos aparte de estos cuatro; de mi experiencia, veo que la raiz del problema está relacionado con el ciclo de vida de sus aplicaciones (ALM), y mejorar sus buenas prácticas. Casi siempre, se puede llegar a una solución mejor diseñada y más segura; de hecho, durante las clases y reuniones, el cliente se queda convencido, pero días después, vuelven a la realidad de su trabajo y por diversas razones, es complicado cambiar los hábitos, y poner en marcha «lo que ya le has convencido» anteriormente.

Aquí pongo un post de Conor Cunningham (http://blogs.msdn.com/conor_cunningham_msft/archive/2008/08/07/foreign-keys-are-our-friends.aspx) donde muestra beneficios ocultos de usar DRI: Utilizando DRI el Optimizador de SQL Server, puede decidir no acceder a la tabla base, porque SQL Server ya sabe — por la Integridad Referencial — que si una fila existe en la tabla hija, tiene que existir en la tabla padre.

Además, me gustaría hacer algunas recomendaciones sobre todo esto, que quizás ves útil.

Usando un script muy similar al de Conor:

use AdventureWorks2008;
go
 

drop table f1
drop table t1
go

create table t1(id int, v char(1) default ‘a’
, constraint t1_pk primary key (id))
go

create table f1(f_id int identity, t_id int not null, v char(1) default ‘a’
, constraint f1_pk primary key (f_id)
, constraint fk1 FOREIGN KEY (t_id) REFERENCES t1(id))

go


create nonclustered index nci_f1_t_id
on f1 (t_id)

declare @i int=0
set nocount on
while @i < 2000
begin
insert into t1 (id) values (@i)
insert into f1 (t_id) values (@i)
insert into f1 (t_id) values (@i)
insert into f1 (t_id) values (@i)
set @i+= 1
end

 

Connor comenta en su post, que las dos siguientes consultas no necesitan acceder a la tabla base — puedes verlo en el plan de ejecución:

/*
|–Index Scan(OBJECT:([AdventureWorks2008].[dbo].[f1].[nci_f1_t_id]))
*/
select f1.*
from t1 inner join f1
on t1.id = f1.t_id 

/*
|–Index Scan(OBJECT:([AdventureWorks2008].[dbo].[f1].[nci_f1_t_id]))
*/
select f1.* from f1 where
exists (select * from t1 where t1.id = f1.t_id)

 

Y me gustaría sugerir al equipo de SQL Server que considere también los dos siguientes casos:

/*
|–Nested Loops(Inner Join)
|–Clustered Index Seek(OBJECT:([AW].[dbo].[t1].[t1_pk]), SEEK:([AW].[dbo].[t1].[id]=(1)) ORDERED FORWARD)
|–Nested Loops(Inner Join, OUTER REFERENCES:([AW].[dbo].[f1].[f_id]))
|–Index Seek(OBJECT:([AW].[dbo].[f1].[nci_f1_t_id]), SEEK:([AW].[dbo].[f1].[t_id]=(1)) ORDERED FORWARD)
|–Clustered Index Seek(OBJECT:([AW].[dbo].[f1].[f1_pk]), SEEK:([AW].[dbo].[f1].[f_id]=[AW].[dbo].[f1].[f_id]) LOOKUP ORDERED FORWARD)       
*/
select f1.* from
t1 inner join f1
on t1.id = f1.t_id
where f1.t_id = 1

/*
|–Merge Join(Inner Join, MERGE:([AW].[dbo].[t1].[id])=([AW].[dbo].[f1].[t_id]), RESIDUAL:([AW].[dbo].[f1].[t_id]=[AW].[dbo].[t1].[id]))
|–Clustered Index Scan(OBJECT:([AW].[dbo].[t1].[t1_pk]), ORDERED FORWARD)
|–Index Scan(OBJECT:([AW].[dbo].[f1].[nci_f1_t_id]), ORDERED FORWARD)
*/
select t1.id, f1.*
from t1 inner join f1
on t1.id = f1.t_id

 

La primera consulta, implementa una consulta tipica OLTP en la que busca todas las filas hija de una clave primaria concreta de la tabla padre. En este ejemplo, SQL Server ya sabes que para cada fila de la tabla hija (f1), por lo menos existe una fila en la tabal padre (t1). La única diferencia con la consulta del post de Connor es que la consulta tiene un predicado WHERE, que yo creo que es lo que hace que se comporte de forma diferente. Yo creo que como el filtro es muy selectivo, y es más selectivo en la tabla padre (1 padre por cada 4 hijos), entonces «coloca» la tabla t1 como muy candidata a ser accedida.

 

La segunda consulta, me resulta un poco sorprendente, porque SQL Server ya sabe que t1.id = f1.t_id. ¿Por qué acceder a la tabal t1, si ya sabe que el valor está también en f1.t_id?

 

Si crees que esta dos «sugerencias lógicas» debería considerarlas SQL Server, haz login en Connect, y vota por la sugerencia (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363429) :).

 

Eladio Rincón