SQL table not obeying its unique constraint

user_5865058 0
Viewed 668

I set up an SQL table in sqlite3 with the following command:

CREATE TABLE "userstrings" (
    'user_id' INTEGER NOT NULL,
    'cat_id' INTEGER NOT NULL,
    'name_id' INTEGER,
    CONSTRAINT unq UNIQUE(user_id, cat_id, name_id)
)

I'm still wiring up the user interface but I noticed in the table there were four rows all with user_id = 10, cat_id = 8, and name_id = NULL.

Does NULL somehow circumvent unique constraints? Ultimately, the goal is to only allow unique rows, and to allow name_id to be null.

sqlite sql
2 Answers

According to the ANSI standards, a UNIQUE constraint should not allow duplicate non-NULL values but does in fact allow multiple NULL values.

The SQL standard requires that a UNIQUE constraint be enforced even if one or more of the columns in the constraint are NULL however SQLite does not enforce this rule.

user_22971160

SQLite does not consider multiple NULL values are counting towards a violation of a unique constraint (nor do several other major database). See here for the SQLite documentation on this.

So using NULL as a placeholder may not behave as intended in the context of a unique constraint. One possible workaround would be to use a different placeholder value, e.g. -1 instead. The following INSERT statement would fail:

INSERT INTO userstrings (user_id, cat_id, name_id)
VALUES
    (1, 1, -1),
    (1, 1, -1);

Whereas had the name_id values both been NULL, the insert would have succeeded.

user_18632290
Related