Field Value Must Be Unique Unless It Is Null

Field value must be unique unless it is NULL

Here is an alternative way to do it with a constraint. In order to enforce this constraint you'll need a function that counts the number of occurrences of the field value. In your constraint, simply make sure this maximum is 1.

Constraint:

   field is null or dbo.fn_count_maximum_of_field(field) < 2

EDIT I can't remember right now -- and can't check it either -- whether the constraint check is done before the insert/update or after. I think after with the insert/update being rolled back on failure. If it turns out I'm wrong, the 2 above should be a 1.

Table function returns an int and uses the following select to derive it

   declare @retVal int

   select @retVal = max(occurrences)
   from ( 
        select field, count(*) as occurrences
        from dbo.tbl
        where field = @field
        group by field
   ) tmp

This should be reasonably fast if your column as a (non-unique) index on it.

Null value isn't unique

Postgres documentation claims that this behaviour is compliant with the SQL standard:

In general, a unique constraint is violated when there are two or more
rows in the table where the values of all of the columns included in
the constraint are equal. However, two null values are not considered
equal in this comparison. That means even in the presence of a unique
constraint it is possible to store duplicate rows that contain a null
value in at least one of the constrained columns. This behavior
conforms to the SQL standard[.]

One possibility is to rethink your schema (to be honest, a uniqueness constraint on name+address+college doesn't make a whole lots of sense in your example).

Set a unique constraint only when a field is null

MySQL supports functional key parts since 8.0.13.

• If your version is sufficiently recent you can define your index as:

  UNIQUE(`user_id`, `test_id`, (IFNULL(`completed_date`, -1)))
  

  (Demo on dbfiddle.uk)

  Note that the above index will also prevent duplciate dates for completed executions. If those should be valid then a slightly modified index would work:

  UNIQUE(`user_id`, `test_id`, (
      CASE WHEN `completed_date` IS NOT NULL
      THEN NULL
      ELSE 0
  END))
  

  (Demo on dbfiddle.uk)

  Although then it starts to feel a bit dirty ;)

• If you have at least version 5.7 you can use a (virtual) generated column as workaround:

  CREATE TABLE `executed_tests` (
      `id` INTEGER AUTO_INCREMENT NOT NULL,
      `user_id` INTEGER NOT NULL,
      `test_id` INTEGER NOT NULL,
      `start_date` DATE NOT NULL,
      `completed_date` DATE,
      `_helper` CHAR(11) AS (IFNULL(`completed_date`, -1)),
      PRIMARY KEY (`id`),
      UNIQUE(`user_id`, `test_id`, `_helper`)
  );
  

  (Demo on dbfiddle.uk)

• If you are stuck on 5.6 then a combination of a regular (non-virtual) column and slightly modified INSERT statements would work:

  CREATE TABLE `executed_tests` (
      `id` INTEGER AUTO_INCREMENT NOT NULL,
      `user_id` INTEGER NOT NULL,
      `test_id` INTEGER NOT NULL,
      `start_date` DATE NOT NULL,
      `completed_date` DATE,
      `is_open` BOOLEAN,
      PRIMARY KEY (`id`),
      UNIQUE(`user_id`, `test_id`, `is_open`)
  );
  

  In this case you would set is_open to true for incomplete executions and to NULL after completion, making use of the fact that two NULLs are treated as not equal.

  (Demo on dbfiddle.uk)

Unique constraint that allows empty values in MySQL

Yes, you can do this. See the MySQL reference (version 5.5).

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

Unique fields that allow nulls in Django

Django has not considered NULL to be equal to NULL for the purpose of uniqueness checks since ticket #9039 was fixed, see:

http://code.djangoproject.com/ticket/9039

The issue here is that the normalized "blank" value for a form CharField is an empty string, not None. So if you leave the field blank, you get an empty string, not NULL, stored in the DB. Empty strings are equal to empty strings for uniqueness checks, under both Django and database rules.

You can force the admin interface to store NULL for an empty string by providing your own customized model form for Foo with a clean_bar method that turns the empty string into None:

class FooForm(forms.ModelForm):
    class Meta:
        model = Foo

    def clean_bar(self):
        return self.cleaned_data['bar'] or None

class FooAdmin(admin.ModelAdmin):
    form = FooForm

How do I create a unique constraint that also allows nulls?

SQL Server 2008 +

You can create a unique index that accept multiple NULLs with a WHERE clause. See the answer below.

Prior to SQL Server 2008

You cannot create a UNIQUE constraint and allow NULLs. You need set a default value of NEWID().

Update the existing values to NEWID() where NULL before creating the UNIQUE constraint.

MySQL: Avoid having to set a NULL value on an UNIQUE field before updating it

Is there a way to avoid setting this NULL?

Not in MySQL. The SQL Standard defines the feature of deferrable constraints that address this specific issue. Unfortunately, MySQL does not implement this section of SQL Specification. Nulls are your only option here.

Now, when a contraint is marked as deferrable (as in PostgreSQL or Oracle), its validation can be deferred to the end of each SQL statement execution, or even to the end of the whole transaction; that is, its full validation happens only at the time of the commit, after all updates have been done and all values would be good again.

As you see, unless you have the option to migrate to PostgreSQL or Oracle (highly unlikely) you are stuck with using nulls.

---

Related Topics