Sqlite Null and Unique

Sqlite NULL and unique?

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:

1. 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.

2. 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.

Composite unique contstraint with null values

You can create a unique partial index for first_name and last_name only when suffix is null:

CREATE UNIQUE INDEX idx_people ON people(first_name, last_name) 
WHERE suffix IS NULL;

See a simplified demo.

If your version of SQLite is 3.31.0+, you could create a generated column which returns an empty string when suffix is null and use it in the unique constraint instead of suffix:

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    suffix TEXT,
    str_suffix GENERATED ALWAYS AS (COALESCE(suffix, '')),
    UNIQUE (first_name, last_name, str_suffix)
);

SQLite 3.6.21: Squeezing out NULL values with a unique column value

Python reorders the "SELECT distinct LastName from foo" so that the NULL is the first value. SQL provides "Smith" as the first value. To ignore the NULL I changed that line to

...THEN (SELECT distinct LastName from foo where LastName is NOT NULL)

EDIT:

Copy from SQL console:

sqlite>
sqlite> SELECT distinct LastName from foo;
Smith

sqlite>

Copy from Python:

with con:
    cur = con.cursor()
    cur.execute("SELECT distinct LastName from foo")
    answer = cur.fetchall()
    print answer

Results in

[(None,), (u'Smith',)]

Why I can add null value to primary key in SQLite?

Thanks to @astentx advice, I check documentation and it said that:

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns.

Sqlite : unique constraint if not empty

In a UNIQUE column, NULL values are considered distinct. (Don't ask why, this is just how SQL works.)

So replace the emtry strings with NULLs.

UPSERT based on UNIQUE constraint with NULL values

If you can find a value that can never legally exist in col3 (make sure with a check constraint), you could use a unique index:

CREATE UNIQUE INDEX ON my_table (
   col2,
   coalesce(col3, -1.0)
);

and use that in your INSERT:

INSERT INTO my_table (col2, col3, col4)
VALUES (p_col2, p_col3, p_col4)
ON CONFLICT (col2, coalesce(col3, -1.0))
DO UPDATE SET col4 = excluded.col4;

---

Related Topics