CONSTRAINT UNIQUE y NULL

[ofl666]

Imaginemos que tengo la tabla que genera la siguiente sentencia:
CREATE TABLE Conductores
(
CodigoConductor NUMBER(4) CONSTRAINT PKConductores PRIMARY KEY,
Nombre CHAR(100) CONSTRAINT NNConductoresNombre NOT NULL,
Apellido1 CHAR(100),
Apellido2 CHAR(100),
UNIQUE (Nombre, Apellido1, Apellido2)
);

Mi intención con la constraint UNIQUE es que no pueda haber 2 registros con el mismo nombre, apellido1 y apellido2. Esto funciona correctamente cuando los registros tienen los 3 campos pero si no tienen apellido1 o apellido2 (solo el nombre es NOT NULL y deseo que siga siendo así) no tiene el funcionamiento que yo esperaba (quizas esperaba demasiado), es decir, ejemlos de registros que entran y que no entran:

INSERT (1,'Pepe','Pepe1','Pepe2') -> Entra
INSERT (2,'Pepe','Pepe1','Pepe2') -> NO entra (correcto, es lo que quiero)

INSERT (3,'Pepe') -> Entra
INSERT (4,'Pepe') -> ¡¡¡¡¡ ENTRA !!!! no lo entiendo

INSERT (5,'Pepe','Pepe1') -> Entra
INSERT (6,'Pepe','Pepe1') -> ¡¡¡¡¡ ENTRA !!!! no lo entiendo

¿Alguien sabe como definir la restricción que yo busco (aparte de con trigers)?

[Mithrandir]

La tabla hace lo que le especificaste en su creación. Ella unicamente revisa que CodigoConductor sea el que no se repita, fuera de ahí no importa nada (salvo que Nombre no sea Nulo).

Intenta crear además un solo indice UNIQUE (sql server) sobre los 3 campos de apellido (sin eliminar la PK sobre CodigoConductor), eso te agregaría el nivel extra de validación.

[ofl666]

Mithrandir, revisa mas en detalle mi sentencia CREATE TABLE, puedes observar como en la última línea se especifica una CONSTRAINT UNIQUE, no se si sabes lo que es esto, si mi intención fuese por ejemplo que el nombre nunca se repitiese, es decir, aplicar la CONSTRAINT a una sola columna funcionaría perfectamente, mi problema es que UNIQUE no funciona como yo creo que cabría esperar si se aplica a varias columnas.

[Mithrandir]

Tienes razón, no observé esa parte de tu script... y me temo que nunca he necesitado lo que comentas, asi que no tengo nada en este momento

[MisterX]

Lo que sucede es que el valor NULL no es 'vacio', es 'indeterminado' y por lo tanto el motor no puede determinar si un NULL es igual a otro, tal vez lo siguiente te funcione:

INSERT (5,'Pepe','Pepe1','') -> Entra
INSERT (6,'Pepe','Pepe1','') -> No deberia entrar

Saludos!!

[ofl666]

Efectivamente MisterX llevas razón, la línea que planteas no entraría (no lo he probado pero debe ser así), pero no me puedo plantar que por cada insert a dicha tabla tenga que mirar si se esta insertando algún NULL y si es así cambiarlo por '', aunque sería viable, el hecho de meter '' cuando debe haber NULL no me da buena espina, imagina que el día de mañana quiero hacer un informe de tipo "Todos los conductores que no tengan apellido2", seguro que dicho día el que se pica esa query no recuerda lo de '' y hace "SELECT campos FROM Conductores WHERE Apellido2 IS NULL" y los que están con '' no entran...
Necesito encontrar una forma "mas normalizada" de hacerlo, en principio a una mala, aunque supuestamente esto nunca debe hacerse, el control lo realizaría desde la interfaz, sería un control muy simple y eficaz pero claro siempre esta el miedo de que seguro que algún cliente de estos que les gusta enredar me abré la base de datos a mano y me inserta el registro como no debe.

[haron]

no se si te podra servir para algo mi consejo, pero creo que indicar una clave unica en los campos "nombre", "apellido1" y "apellido2" no sea una buena idea.

busca por ejemplo en el Google:

"Antonio Garcia Lopez"

veras que aparecen un monton de tipos que no son nisiquiera hermanos y se llaman igual.

quizas deberias introducir un nuevo campo (DNI, NIF o algo asi) y crear una clave unica sobre ese campo.

[ofl666]

Creo Haron que llevas toda la razón, lo que pasa es que decirle al cliente que tiene que introducir un nuevo campo cada vez que crea un conductor se que no le va a gustar, de hecho ya nos costo convencerle de que Nombre, Apellido1 y Apellido2 tenían que ser 3 "cajas"(como él las llama) y no una, pero la verdad es que me parece la mejor solución.

De todas formas, aunque en este caso se pueda solventar así, ¿no crees que tiene que haber alguna forma de hacer lo que yo pretendía?, lo digo por si en un futuro me toca haver algo similar saber hacerlo.

Gracias

[haron]

si estas trabajando con una buena base de datos, como Oracle, lo que se me ocurre es definir un 'trigger' o disparador que se active justo antes de insertar o modificar un registro.

en ese trigger escribirias algo como esto:

Código:

select :existe
from conductores
where nombre = :parametroNombre
and apellido1 = :parametroApellido1
and apellido2 = :parametroApellido2;

if :existe is not null then
  raise_exception "Conductor ya existe"
end_if

el codigo anterior es solo orientativo, hace un monton de tiempo que no trabajo con Oracle y no se exactamente como escribirlo.

otra forma seria hacer lo mismo a nivel aplicacion.

[haron]

bueno, creo que lo anterior tampoco funcionaria, ya que si alguno de los parametros en la consulta es nulo, el motor descartaria ese registro. quizas funcione esta consulta:

Código:

select :existe
from conductores
where nvl(nombre, "") = nvl(:paramNombre, "")
and nvl(apellido1, "") = nvl(:paramApellido1, "")
and nvl(apellido2, "") = nvl(:paramApellido2, "")

donde "nvl" es una funcion propia de oracle que sustituye un valor por otro en caso de que sea nulo.

[ofl666]

En total veo 4 cauces posibles:
-Añadir el campo NIF: en este caso concreto, como te comentaba anteriormente, creo que es lo mejor, por lo que intentare convencer al cliente.

-Hacer el control desde la interfaz: no me gusta nada la idea por si alguien entra directamente en la base de datos.

-Meter '' en lugar de NULL: tampoco me gusta la idea por lo que ya comente de que a futuro tendre problemas.

-Triger: la única opción posible de hacer exactamente lo que quería.

Total, que intentare convencer al cliente de añadir el NIF y que si me surge otro caso donde necesite algo similar pero no exista la opción de añadir un campo utilizare un triger.

Muchísimas gracias