Menu
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?
782
You can insert NULL values into columns with the UNIQUE constraint because NULL is the absence of a value, so it is never equal to other NULL values and not considered a duplicate value. This means that it's possible to insert rows that appear to be duplicates if one of the values is NULL .
Unique fields in SQL Server are created using unique constraints or unique indexes, furthermore, each unique constraint uses a unique index. Regardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value.
Based on the SQL standard, PRIMARY KEY should always imply NOT NULL . However, SQLite allows NULL values in the PRIMARY KEY column except that a column is INTEGER PRIMARY KEY column or the table is a WITHOUT ROWID table or the column is defined as a NOT NULL column.
The columns specified in a unique constraint must be defined as NOT NULL. The database manager uses a unique index to enforce the uniqueness of the key during changes to the columns of the unique constraint. Unique constraints can be defined in the CREATE TABLE or ALTER TABLE statement using the UNIQUE clause.
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.
132
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
