Sqlite NULL and unique?

Question

I noticed that I can have NULL values in columns that have the UNIQUE constraint: UNIQUE(col)

Would that generate any issues in certain situations?

"Would that generate any issues in certain situations?" Probably - which situations are you concerned about? — D Stanley, Mar 27 '14 at 21:20
Like doing any kind of db operations. Would I get any errors? I was thinking to set records to NULL when I don't need them anymore. The thing is that deleting them takes a huge amount of time and I would rather do that in a cron job — katie, Mar 27 '14 at 21:26
So the issue there is any queries that don't filter out NULL records would see the "deleted" ones. But that's an application issue, not a database issue. I would do more research on why deleting takes so long - `DELETE` operations should be as fast as finding the records. — D Stanley, Mar 27 '14 at 21:27
I already know why. It's because of foreign key constrains and some triggers attached to each related record :( — katie, Mar 27 '14 at 21:38

score 47 · Accepted Answer · edited Jun 20 '20 at 09:12

While the following addresses multiple null values, it does not address any "issues" associated with such a design, other than possible database/SQL portability - as such, it should probably not be considered an answer, and is left here merely for reference.

This is actually covered in the SQLite FAQ. It is a design choice - SQLite (unlike SQL Server) chose that multiple NULL values do not count towards uniqueness in an index.

The SQL standard requires that a UNIQUE constraint be enforced even if one or more of the columns in the constraint are NULL, but SQLite does not do this. Isn't that a bug?

Perhaps you are referring to the following statement from SQL92:

A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.

That statement is ambiguous, having at least two possible interpretations:

A unique constraint is satisfied if and only if no two rows in a table have the same values and have non-null values in the unique columns.

A unique constraint is satisfied if and only if no two rows in a table have the same values in the subset of unique columns that are not null.

SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle, and Firebird. It is true that Informix and Microsoft SQL Server use interpretation (2), however we the SQLite developers hold that interpretation (1) is the most natural reading of the requirement and we also want to maximize compatibility with other SQL database engines, and most other database engines also go with (1), so that is what SQLite does.

See a comparison of NULL handling.

I can't think of a situation in which (1) would be the *useful* interpretation. — Rei Miyasaka, Jul 01 '20 at 21:50
I have one: Users have a key card with a unique id, which means no two users can have the same key. But some users don't have a key card anymore (NULL). And they might have given their key card to another user. — Johannes Egger, Jun 18 '21 at 11:37
@JohannesEgger One of those the times when SQL Server's filtered indices (https://stackoverflow.com/a/15206061/2864740) becomes very useful for behavior (1) semantics. — user2864740, Jun 21 '21 at 20:26

score 11 · Answer 2 · answered Apr 15 '18 at 20:27

11

If you want your unique index to throw an error when a two rows would be the same if you disregard NULL columns (and don't want to use triggers from Satyam's answer) you can do something like this

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

answered Apr 15 '18 at 20:27

HH321

502
1
6
20

The ifnull() solution also causes inserts to fail for me on 'INSERT INTO `test`(`Field1`,`Field2`) VALUES (2,3);' and I'm not sure why, but it's not what I expected. I solved the problem in the application instead of in the database, but I wanted to put that here for anyone who came after me. – Steve V. Nov 08 '20 at 03:29
Another option to to use a filtered index (https://stackoverflow.com/a/15206061/2864740). – user2864740 Jun 21 '21 at 20:30
you're right, here is a better link https://www.sqlite.org/partialindex.html, also see the note on when it can be used – HH321 Jun 22 '21 at 21:36
Nice trick. However, the code sample could be improved on by dropping the unnecessary back-ticks (or at least replacing them with the standard double quotes) and using the standard `coalesce(Field2,0)` instead of `ifnull()`. – Manngo Oct 11 '21 at 00:46

score 4 · Answer 3 · answered Oct 18 '16 at 05:18

You can create 2 triggers on table to check if a row with column as null exists before any insert or update operation, if so raise exception.

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;

Sqlite NULL and unique?

3 Answers3

Linked