Campos con valor único en SQL Server

Supongamos que necesitamos que un campo de una tabla en SQL server tenga un valor único ¿que hacemos? Hay dos opciones para esto, declararlo clave principal o declararlo como índice único, según sea el caso y según lo que queramos se puede aplicar una u otra definición.

Compliquémoslo un poco más, supón que el campo admite valores nulos con lo que las definiciones anteriores de índice primario o índice único no se pueden aplicar, aún así quieres que si el valor no sea nulo sea único.

Pongamos un ejemplo en concreto, tenemos 3 tablas relacionadas, localizaciones que tiene entre sus campos el código del país, y países que tiene una referencia al continente al que pertenece.

diseño-tablas

El problema por ejemplo es que queremos que el código postal en las localizaciones sea único (luego lo complicaremos más) pero no disponemos de todos los códigos postales por tanto puede tener valor nulo este campo.

Existe dos opciones para solucionar esto, iremos de la más difícil e ineficiente a la más sencilla y rápida.

SQL Server triggers ( desencadenadores o disparadores )

Crear un trigger (desencadenador o disparador) en SQL server para controlar valores único de un campo de una tabla puede ser realmente una labor bastante tediosa y complicada según el nivel de nuestra base de datos.

Para crear un trigger utilizaremos el SQL Server Managment Studio, vamos a la tabla en la que queremos crear el disparador, click derecho y en la opción desencadenadores damos en nuevo desencadenador, esto nos crea una plantilla por defecto en la que tenemos que rellenar algunos datos y programar el disparador.

desencadenador

En el siguiente enlace tenemos todos los código necesarios para crear un trigger que comprueba las claves duplicadas al insertar un registro.

Este script tiene algunas dificultades:

  1. puede que no necesitemos insertar los datos malos dentro de otra tabla
  2. si el INSERT se ejecuta dentro de una transacción es posible que nos interese que lance un error en vez de que inserte los datos en otra tabla y que cancele la transacción
  3. está asumiendo que siempre se van a insertar el mismo número de campos en la consulta INSERT y puede ser que no siempre insertemos lo mismo.
  4. para los INSERT en grupos si el campo está duplicado no lo inserta, tal vez nos interesaría que inserte uno y elimine el otro, en cuyo caso tendríamos que ver cuál insertar.

Soluciones:

  1. con eliminar las líneas de inserción tendríamos suficiente.
  2. en el último comentario de el post de control de registros duplicados sin utilizar índices vemos la utilización de funciones como RAISERROR, ROLLBACK TRANSACTION y RETURN para lanzar el error, cancelar la transacción y salir del trigger.
  3. para esto en vez de utilizar INSERT INTO Tabla SELECT <campos> FROM INSERTED podríamos utilizar INSERT INTO Tabla SELECT * FROM INSERTED, la única desventaja es que estaría incluida la clave principal con lo que si tenemos restringida la inserción manual de la misma daría un error del tipo An explicit value for the identity column in table ‘nombre_tabla' can only be specified when a column list is used and IDENTITY_INSERT is ON. (Corregir esto os lo dejo de tarea como el punto 4).

En el enlace anterior también se puede ver el código para realizar el trigger en el caso del update, la gran desventaja de este es que si los campos a actualizar son diferentes cada vez este script no es válido y realizar uno para estos casos realmente no se me ocurre ahora y me parece algo muy engorroso.

Antes de ver la solución ideal quiere recapitular y mencionar algunas cosas que es posible se hayan quedado en el tintero sobre los desencadenadores para controlas los valores únicos sin utilizar índices. Por ejemplo que SQL Server al crear un trigger tiene 2 tablas muy útiles que guardan los valores a insertar o barrar y que tienen la misma estructura de campos que la tabla de destino, estas tablas son INSERTED y UPDATED, en el siguiente enlace podemos ver como utilizar las tablas INSERTED y UPDATED desde la web de Microsoft.

Ahora ¿y si pudiéramos crear nosotros mismos la funcionalidad del índice único en este tipo de casos? Pues sí, en SQL Server esto es posible gracias a la unión de dos funcionalidades, las funciones y las restricciones, pero para ello debemos tener algunas nociones básicas de funciones en SQL server, así cómo del uso de variables y algo de programación.

SQL Server functions (funciones) y constraint (restricciones)

Veamos un código básico que solo comprueba que sea único el valor:

USE [tabla]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ChkD](@code varchar(50))
RETURNS int
AS 
BEGIN
	DECLARE @total int
	
	SELECT @total = IsNull(COUNT(*), 0) 
	FROM paises 
	WHERE codigo_postal like @code 
	RETURN @total
END
GO


Este código tiene muchas deficiencias, por ejemplo si es una tabla que ya existe y es muy grande va a ejecutar (la primera vez) para todos los valores este script cuando realmente debería hacerlo para los valores NO nulos y así optimizaríamos el proceso.



Esto lo podríamos resolver en la segunda parte del código, al crear la restricción porque sin ella la función por si sola no hace nada, la restricción para omitir los valores nulos sería la siguiente:



ALTER TABLE paises
ADD CONSTRAINT DF_code_unico 
CHECK (codigo_postal IS NULL OR dbo.ChkD(codigo_postal) <= 1)
GO 


Pero compliquémoslo más, pues hemos descubierto que el código postal si es único, pero es único en un país o pongamos en un continente, así que un código tiene que ser único en un continente pero por si solo si puede estar duplicado.



En este código se comprueba este valor adicional y además que si es nulo no haga nada:



ALTER FUNCTION [dbo].[ChkD](@code varchar(50), @country int)
RETURNS int
AS 
BEGIN
	DECLARE @total int
	DECLARE @check int
	DECLARE @refer varchar(25)
	DECLARE @lengt int 
	
	SET @check = DATALENGTH(@code)
	
	IF(@check > 0) BEGIN
		
		SELECT @refer = continente
		FROM paises 
		WHERE pais_id = @country
		
		SET @lengt = DATALENGTH(@refer)
		
		IF(@lengt > 0) BEGIN
		
			SELECT @total = IsNull(COUNT(*), 0) 
			FROM paises 
			INNER JOIN continentes ON ...
			INNER JOIN localizaciones ON ...
			WHERE codigo_postal like @code 
			AND continente_id <> @refer 
			RETURN @total
			
		END
		
		RETURN 0
		
	END
	
	RETURN 0

END


La restricción ahora cambiaría y nos bastaría con poner:



ALTER TABLE paises
ADD CONSTRAINT DF_codigo_unico 
CHECK (dbo.ChkD(codigo_postal,pais) < 1)
GO


Aquí les dejo la información referente a las funciones IsNull y DATALENGTH



Importante: nunca comparar las variables utilizando expresiones del tipo @a IS NULL o @a <> NULL porque no son válidas, lo más seguro para saber si una variable está vacía es la utilización de estas dos funciones anteriores.



Espero que os haya sido de mucha utilidad, tanto como a mi.

Comentarios

Publicar un comentario