Restricción UNIQUE - ¿ como Aceptar + de un valor NULL ??

Hola, una opcion seria

Te armas una UDF y luego armas una restriccion Check que use la UDF :-)


Maxi - Buenos Aires - Argentina
Desarrollador 3 Estrellas

Msn_messager:
mail: Maxi.da[arroba]gmail.com

"Marcelo Clavero" escribió en el mensaje
news:

Estimados: Hola a Todos

Es posible tener una columna de una tabla de manera que acepte solo NULLS
o
valores Únicos (cuando son <> NULL) ? Quería hacerlo sin usar triggers o
UDFs en la restricciónentonces leí en los BOL que las restricciones
UNIQUE admiten columnas NULL (a diferencia de los indices de PK), entonces
probé, pero al probar, solo deja poner una sola fila con NULL, y a la
segunda salta el error de la restricción Unique.

Cómo lo harían ustedes ???

Un ejemplo práctico con una muestra de datos:

Id_clave Id_valor
1 NULL
2 13
3 20

Yo querría que al agregar:
4 13 me dé error porque 13 ya existe en Id_valor (eso
funcionó con la Restriccón Unique)
y al agregar:
4 NULL no me dé error (esto NO FUNCIONÓ).

Espero haberme explicado bien.

Les agradezco desde ya el esfuerzo y vayan mis respetos a los "genios" que
asiduamente nutren este news, ayudando a
entender de qué se trata el mundillo de las BD.

Marcelo Clavero

Marcelo,

Existen varias formas de implementar este caso especifico.

- Crear una columna calculada (computed column) la cual toma el valor de la
clave primaria cuando el valor de la columna en question es null o toma el
valor de la columna en question cuando esta no es null.

- Crear una vista con los valores no nulos de la tabla y crear un indice
clustered y unico por la columna en question. Fijate que aqui el indice solo
incluye los valores no nulos y que la vista solo incluye la columna c2,
aunque pudieramos poner todas las columnas en la vista, pero solo incluye las
filas con c2 is not null.

- Crear una funcion que espere como parametro la clave primaria y el valor
de la columna en question para chequear existencia. La funcion sera ejecutada
para operaciones insert / update, lo cual podria incidir en el rendimiento de
estas operaciones si lo comparamos por ejemplo con el primer metodo.

Aca te paso un ejemplo de cada metodo, pruebalos para ver cual da mejor
resultado en tu ambiente.

create table t1 (
c1 int not null identity primary key,
c2 int null,
c3 as case when c2 is null then c1 else c2 end,
constraint u_t1_c2_c3 unique(c3, c2)
)
go

insert into t1 default values
insert into t1 default values
insert into t1 default values
insert into t1 default values
go

insert into t1(c2) values(1)
go

insert into t1(c2) values(1)
go

insert into t1(c2) values(2)
go

insert into t1 default values
insert into t1 default values
go

select * from dbo.t1
where c3 = 2 and c2 = 2
go

alter table t1
drop constraint u_t1_c2_c3
go

alter table t1
drop column c3
go

create view dbo.v1
with schemabinding
as
select c1, c2
from dbo.t1
where c2 is not null
go

create unique clustered index ix_u_v1_c2 on dbo.v1(c2)
go

insert into t1(c2) values(1)
go

insert into t1(c2) values(3)
go

insert into t1 default values
insert into t1 default values
go

select * from dbo.t1
where c2 = 2
go

drop view dbo.v1
go

create function dbo.f1 (
@pk int,
@c2 int
)
returns int
as
begin
return (case when exists (select * from dbo.t1 where c1 != @pk and c2 = @c2)
then 1 else 0 end)
end
go

alter table t1
add constraint chk_t1_c2 check (dbo.f1(c1, c2) = 0)
go

create nonclustered index ix_t1_c2 on t1(c2)
go

insert into t1(c2) values(1)
go

insert into t1(c2) values(4)
go

insert into t1 default values
insert into t1 default values
go

select * from t1
where c2 = 2
go

drop table t1
go

drop function dbo.f1
go


AMB


"Marcelo Clavero" wrote:

Estimados: Hola a Todos

Es posible tener una columna de una tabla de manera que acepte solo NULLS o
valores Únicos (cuando son <> NULL) ? Quería hacerlo sin usar triggers o
UDFs en la restricciónentonces leí en los BOL que las restricciones
UNIQUE admiten columnas NULL (a diferencia de los indices de PK), entonces
probé, pero al probar, solo deja poner una sola fila con NULL, y a la
segunda salta el error de la restricción Unique.

Cómo lo harían ustedes ???

Un ejemplo práctico con una muestra de datos:

Id_clave Id_valor
1 NULL
2 13
3 20

Yo querría que al agregar:
4 13 me dé error porque 13 ya existe en Id_valor (eso
funcionó con la Restriccón Unique)
y al agregar:
4 NULL no me dé error (esto NO FUNCIONÓ).

Espero haberme explicado bien.

Les agradezco desde ya el esfuerzo y vayan mis respetos a los "genios" que
asiduamente nutren este news, ayudando a
entender de qué se trata el mundillo de las BD.

Marcelo Clavero

Muchas gracias.
Imponente lo de ustedes.


"Alejandro Mesa" escribió en el
mensaje news:

Marcelo,

Existen varias formas de implementar este caso especifico.

- Crear una columna calculada (computed column) la cual toma el valor de

la

clave primaria cuando el valor de la columna en question es null o toma el
valor de la columna en question cuando esta no es null.

- Crear una vista con los valores no nulos de la tabla y crear un indice
clustered y unico por la columna en question. Fijate que aqui el indice

solo

incluye los valores no nulos y que la vista solo incluye la columna c2,
aunque pudieramos poner todas las columnas en la vista, pero solo incluye

las

filas con c2 is not null.

- Crear una funcion que espere como parametro la clave primaria y el valor
de la columna en question para chequear existencia. La funcion sera

ejecutada

para operaciones insert / update, lo cual podria incidir en el rendimiento

de

estas operaciones si lo comparamos por ejemplo con el primer metodo.

Aca te paso un ejemplo de cada metodo, pruebalos para ver cual da mejor
resultado en tu ambiente.

create table t1 (
c1 int not null identity primary key,
c2 int null,
c3 as case when c2 is null then c1 else c2 end,
constraint u_t1_c2_c3 unique(c3, c2)
)
go

insert into t1 default values
insert into t1 default values
insert into t1 default values
insert into t1 default values
go

insert into t1(c2) values(1)
go

insert into t1(c2) values(1)
go

insert into t1(c2) values(2)
go

insert into t1 default values
insert into t1 default values
go

select * from dbo.t1
where c3 = 2 and c2 = 2
go

alter table t1
drop constraint u_t1_c2_c3
go

alter table t1
drop column c3
go

create view dbo.v1
with schemabinding
as
select c1, c2
from dbo.t1
where c2 is not null
go

create unique clustered index ix_u_v1_c2 on dbo.v1(c2)
go

insert into t1(c2) values(1)
go

insert into t1(c2) values(3)
go

insert into t1 default values
insert into t1 default values
go

select * from dbo.t1
where c2 = 2
go

drop view dbo.v1
go

create function dbo.f1 (
@pk int,
@c2 int
)
returns int
as
begin
return (case when exists (select * from dbo.t1 where c1 != @pk and c2 @c2)
then 1 else 0 end)
end
go

alter table t1
add constraint chk_t1_c2 check (dbo.f1(c1, c2) = 0)
go

create nonclustered index ix_t1_c2 on t1(c2)
go

insert into t1(c2) values(1)
go

insert into t1(c2) values(4)
go

insert into t1 default values
insert into t1 default values
go

select * from t1
where c2 = 2
go

drop table t1
go

drop function dbo.f1
go


AMB


"Marcelo Clavero" wrote:

> Estimados: Hola a Todos
>
> Es posible tener una columna de una tabla de manera que acepte solo

NULLS o

> valores Únicos (cuando son <> NULL) ? Quería hacerlo sin usar triggers o
> UDFs en la restricciónentonces leí en los BOL que las restricciones
> UNIQUE admiten columnas NULL (a diferencia de los indices de PK),

entonces

> probé, pero al probar, solo deja poner una sola fila con NULL, y a la
> segunda salta el error de la restricción Unique.
>
> Cómo lo harían ustedes ???
>
> Un ejemplo práctico con una muestra de datos:
>
> Id_clave Id_valor
> 1 NULL
> 2 13
> 3 20
>
> Yo querría que al agregar:
> 4 13 me dé error porque 13 ya existe en Id_valor (eso
> funcionó con la Restriccón Unique)
> y al agregar:
> 4 NULL no me dé error (esto NO FUNCIONÓ).
>
> Espero haberme explicado bien.
>
> Les agradezco desde ya el esfuerzo y vayan mis respetos a los "genios"

que

> asiduamente nutren este news, ayudando a
> entender de qué se trata el mundillo de las BD.
>
> Marcelo Clavero
>
>
>