Unique constraint, but only if not null

Question

Is it possible to make an unique constraint that only takes effect when a colum is not NULL?

Here's a example table:

CREATE TABLE tbl(
  col1 TEXT,
  col2 TEXT,
  col3 TEXT,
  UNIQUE(col1, col2, col3)
);

so if col2 is NULL, I want it to be treated like [col1, anything, col3]

Use MS SQL Server, to "fix" the "problem" . See also http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server/21505140#21505140 — Neil McGuigan, Mar 21 '15 at 20:37

score 3 · Answer 1 · answered Mar 20 '15 at 19:47

3

As the SQLite documentation explains, this is the default behavior for that and many other databases:

If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error. For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the standard is ambiguous) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and Microsoft SQL Server follow the other interpretation of the standard.

So, you can just create the index as you want to.

answered Mar 20 '15 at 19:47

Gordon Linoff

1,242,037
58
646
786

Interesting. NULL values make the SQLite unique index non-unique. It seems better from the point of view of the database analyst to require all unique indexes to be made up of NOT NULL fields. – Gilbert Le Blanc Mar 20 '15 at 20:20
@GilbertLeBlanc . . . If someone wants that, they can readily define the column with a `NOT NULL` constraint. (Although I have an aesthetic appreciation that unique indexes should not have NULL values for the keys, there are times where it is handy.) – Gordon Linoff Mar 21 '15 at 00:04
@GilbertLeBlanc null != null, nor should it – Neil McGuigan Mar 21 '15 at 20:38

score 0 · Answer 2 · answered Mar 20 '15 at 18:33

I don't know the answer for sqlite. There are several DBMS products out there for which this is precisely the default. If a UNIQUE constraint is defined, it still allows one or more entries with one or more of the columns in question to be NULL.

I'm not sure, but I think there are a few DBMS products out there that go the opposite way, sort of. They won't allow an entry with a NULL value, unless uniqueness can be proven with the not null values. The idea is that, when there are NULLS present, the answer to is_unique might be UNKNOWN.

Unique constraint, but only if not null

2 Answers2