programacion - Sqlite NULL y único?

sqlite is null

Question

Noté que puedo tener valores NULL en columnas que tienen la restricción UNIQUE(col) : UNIQUE(col)

¿Eso generaría algún problema en ciertas situaciones?

Answer 1

Puede crear 2 activadores en la tabla para verificar si existe una fila con columna como nula antes de cualquier operación de inserción o actualización, si es así, generar una excepción.

CREATE TRIGGER UniqueColumnCheckNullInsert
        BEFORE INSERT
        ON ''Tablename''
        WHEN NEW.''column_name'' IS NULL
        BEGIN
        SELECT CASE WHEN((
            SELECT 1
            FROM ''Tablename''
            WHERE ''column_name'' IS NULL
            )
            NOTNULL) THEN RAISE(ABORT, "error row exists") END;
    END;

CREATE TRIGGER UniqueColumnCheckNullUpdate
        BEFORE UPDATE
        ON ''Tablename''
        WHEN NEW.''column_name'' IS NULL
        BEGIN
        SELECT CASE WHEN((
            SELECT 1
            FROM ''Tablename''
            WHERE ''column_name'' IS NULL
            )
            NOTNULL) THEN RAISE(ABORT, "error row exists") END;
    END;

Answer 2

Si bien lo siguiente aborda varios valores nulos, no aborda ningún "problema" asociado con tal diseño, aparte de la posible portabilidad de base de datos / SQL, como tal, probablemente no debería considerarse una respuesta, y se deja aquí simplemente como referencia.

Esto está cubierto en las preguntas frecuentes de SQLite. Es una opción de diseño: SQLite (a diferencia de SQL Server) eligió que múltiples valores NULL no cuentan para la singularidad en un índice.

El estándar SQL requiere que se aplique una restricción UNIQUE incluso si una o más de las columnas en la restricción son NULL, pero SQLite no hace esto. ¿No es eso un error?

Quizás te refieres a la siguiente declaración de SQL92:

• Se cumple una restricción única si y solo si no hay dos filas en una tabla que tengan los mismos valores no nulos en las columnas únicas.

Esa afirmación es ambigua, teniendo al menos dos interpretaciones posibles:

1. Se cumple una restricción única solo si no hay dos filas en una tabla que tengan los mismos valores y que no tengan valores nulos en las columnas únicas.

2. Se cumple una restricción única si y solo si no hay dos filas en una tabla que tengan los mismos valores en el subconjunto de columnas únicas que no sean nulas.

SQLite sigue la interpretación (1), al igual que PostgreSQL, MySQL, Oracle y Firebird. Es cierto que Informix y Microsoft SQL Server utilizan la interpretación (2); sin embargo, los desarrolladores de SQLite consideramos que la interpretación (1) es la lectura más natural del requisito y también queremos maximizar la compatibilidad con otros motores de bases de datos SQL, y la mayoría de los otros. los motores de base de datos también van con (1), así que eso es lo que hace SQLite.

Ver una comparación de manejo NULL .

Answer 3

Si desea que su índice único genere un error cuando dos filas serían iguales si no tiene en cuenta las columnas NULL (y no desea utilizar los desencadenantes de la respuesta de Satyam), puede hacer algo como esto

CREATE TABLE `test` (
    `Field1`    INTEGER,
    `Field2`    INTEGER
);
CREATE UNIQUE INDEX `ix` ON `test` (
    `Field1`,
    `Field2`
);
INSERT INTO `test`(`Field1`,`Field2`) VALUES (1,NULL);
INSERT INTO `test`(`Field1`,`Field2`) VALUES (1,NULL); -- This shouldn''t be allowed

DROP INDEX IF EXISTS `ix`;
CREATE UNIQUE INDEX `ix2` ON `test` (
    `Field1`,
    ifnull(`Field2`, 0)  --use this instead
); --will fail