Bases de datos

SQL Server

Índices únicos con excepciones en SQL Server

Última actualización: 14-09-2024 20:22

En este enlace se puede ver la definición del comando CREATE INDEX del Transact-SQL de SQL Server.

En esa definición ya se puede ver que hay una opción "WHERE" en la que se menciona que se pueden poner filtros.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
                    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
                    [ INCLUDE ( column_name [ ,...n ] ) ]
                    [ WHERE <filter_predicate> ]
                    [ WITH ( <relational_index_option> [ ,...n ] ) ]
                    [ ON { partition_scheme_name ( column_name )
                         | filegroup_name
                         | default
                         }
                    ]
                    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
 
                [ ; ]

Resumiendo lo que tenemos es que utilizando la sintaxis básica de creación de un índice único sobre la columna de una tabla se pueden agregar condiciones para definir las excepciones en un WHERE al final del comando:

create unique index [NOMBRE DEL INDICE]
        on [TABLA] (COLUMNA)
        WHERE [CONDICIÓN 1...]
                [AND/OR] [CONDICIÓN 2...]
                [AND/OR] [CONDICIÓN N...];

Notas importantes
• Algo curioso es la forma en que se admiten las condiciones en el WHERE
  • La condición para poner que los NULOS se admiten como excepción del índice único no funciona si se pone NOT COLUMNA IS NULL pero si se pone COLUMNA IS NOT NULL
  • Para agregar como excepción el valor "XYZ" la condición no funciona anteponiendo el NOT a la condición ni al símbolo de IGUAL
    • NOT COLUMNA = 'XYZ'
    • COLUMNA NOT = 'XYZ'
    Pero si funciona de esta formas:
    • COLUMNA != 'XYZ'
    • COLUMNA <> 'XYZ'

Versiones MSSQL en que se ha verificado el funcionamiento de estas notas:

• SQL Server Express 2019
• SQL Server Express 2017
• SQL Server Express 2016
• SQL Server Express 2014
• SQL Server Express 2012
• SQL Server Express 2008 R2

El ejemplo...

En este script que se comparte como ejemplo de la creación de un índice único con excepciones y las pruebas sobre el mismo se está publicando esto:

• Se crea una tabla [persona__test] con columnas de identificador de registro, nombre de persona y un valor identificador de la persona. (En este caso se hace pensando el NIT -"Número de Identificación Tributaria" de Guatemala)...
• Se crea un índice único con estas excepciones:
  • Se permitirá que las personas no tengan valor en columna IDENTIFICADOR. (EXCEPCIÓN => VALOR NULO)
  • Se permitirá que el NIT tenga como valor repetido válido "CF" -CONSUMIDOR FINAL-. (EXCEPCIÓN => VALOR != "CF")
  • Se permitirá que el NIT tenga como valor repetido válido "9999999". (EXCEPCIÓN => VALOR != "9999999")
• Se insertan valores de prueba de personas poniendo casos repetidos de IDENTIFICADOR igual a NULO, "CF" y "9999999" sin problemas.
• Se intenta insertar de nuevo a una versona con un IDENTIFICADOR igual a "312" que es un valor que ya se ha registrado en la tabla.
  • Este INSERT se hace utilizando TRY CATCH para evitar el mensaje abrupto de error.
  • Se muestra el mensaje de error que se recibe porque no se está respetando el índice único que se ha creado.
• Se inserta una persona más, con un IDENTIFICADOR que no se repite.
• Se muestra el contenido de la tabla.

El resultado de este script de prueba aparece al final de esta página.

if not OBJECT_ID('persona__test') is null drop table persona__test;
go
 
create table persona__test (
	ID_PERSONA int identity (1, 1) not null,
	NOMBRE varchar(100),
	IDENTIFICADOR varchar(100) null
	primary key (ID_PERSONA)
);
go
 
create unique index ui_persona__test_IDENTIFICADOR on persona__test (IDENTIFICADOR) 
where  IDENTIFICADOR is not null
	and IDENTIFICADOR != 'CF' 
	and IDENTIFICADOR != '9999999';
go
 
insert into persona__test (NOMBRE, IDENTIFICADOR)
	values ('JUAN', '123')
			, ('PACO', 'CF')
			, ('PEDRO', 'CF')
			, ('MAR', NULL)
			, ('TOTO', '312')
			, ('PEPE', NULL)
			, ('KAKA', '9999999')
			, ('QUQU', '9999999')
			, ('ZAZA', '9999999');
go
 
begin try
    insert into persona__test (NOMBRE, IDENTIFICADOR) values ('TUTU', '312');
end try
begin catch    
    select ERROR_NUMBER() AS ErrorNumber
			, ERROR_SEVERITY() AS ErrorSeverity
			, ERROR_STATE() AS ErrorState
			, ERROR_PROCEDURE() AS ErrorProcedure
			, ERROR_LINE() AS ErrorLine
			, ERROR_MESSAGE() AS ErrorMessage;
end catch;
go
 
insert into persona__test (NOMBRE, IDENTIFICADOR) values ('RERE', '3312');
go
 
 
select * from persona__test order by IDENTIFICADOR, NOMBRE;
go
 

    
        (9 rows affected)
        
        
        (0 rows affected)
        
        ErrorNumber ErrorSeverity ErrorState  ErrorProcedure ErrorLine   ErrorMessage
        ----------- ------------- ----------- -------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        2601        14            1           NULL           4           Cannot insert duplicate key row in object 'dbo.persona__test' with unique index 'ui_persona__test_IDENTIFICADOR'. The duplicate key value is (312).
        
        (1 row affected)
        
        
        (1 row affected)
        ID_PERSONA  NOMBRE    IDENTIFICADOR
        ----------- --------- --------------
        4           MAR       NULL
        6           PEPE      NULL
        1           JUAN      123
        5           TOTO      312
        11          RERE      3312
        7           KAKA      9999999
        8           QUQU      9999999
        9           ZAZA      9999999
        2           PACO      CF
        3           PEDRO     CF
        
        (10 rows affected)
        
        
        Completion time: 2024-09-14T13:04:20.6560109-06:00