0

I need to allow NULL in enc_id, but if values are not null, I need those values to be unique. Here's my model:

class Intake(models.Model):
    id = models.AutoField(primary_key=True)
    enc_id = models.IntegerField(blank=True, null=True, unique=True)
    enc_date = models.DateField(null=True)
    enrollment = models.ForeignKey('Enrollment')

Error when trying to add another instance without an enc_id:

django.db.utils.IntegrityError: ('23000', "[23000] [FreeTDS][SQL Server]Violation of UNIQUE KEY constraint 'UQ__caseload__E136D21F4222D4EF'. Cannot insert duplicate key in object 'dbo.caseload_intake'. The duplicate key value is (<NULL>). (2627) (SQLExecDirectW)")

According to what I've read (including this and a few resolved Django issues), having blank=True, null=True, unique=True should allow me to have duplicate NULLs, but no go. I've recreated my DB just in case and it still raises the integrity error.

I'm running Django 1.10 and MS SQL Server 10. Any ideas?

Community
  • 1
  • 1
Rosie
  • 37
  • 7
  • 2
    You have a unique index on the column. This means you can't have more than 1 null. That is how it works. There is no "work around" for this. For you to have multiple NULL rows you will have to remove the unique index. – Sean Lange Feb 14 '17 at 19:27

2 Answers2

2

For those who come up against this in the future - this is a MS SQL Server limitation. ANSI standards require that a UNIQUE index contains no duplicate values - SQL Server (but not other databases) consider NULL is equal to NULL - many other databases (e.g. Postgres) and the ANSI standard from 92 onward consider NULL to be an unknowable value, which is not the same as a different unknowable value -- consider in SQL SELECT WHERE __ IS NULL vs WHERE ___ = NULL. SQL Server has an ANSI_NULLS flag, which in effect forces the standards-compliant use of IS (NOT) NULL rather than xyz =/<> NULL in queries, but this doesn't have the same effect in indexes.

More non-Django-specific info here: https://www.sqlservergeeks.com/sql-server-unique-constraint-multiple-null-values/

Jamie
  • 816
  • 4
  • 6
0

Rosie,

it doesn't make a lot of sense to me to have a unique field where you can have duplicate NULLs. What about you remove the constraint and reinforce this rule by other means?

For example, you could override the method save in order to do that..

def save(self, *args, **kwargs):
    # place your logic here

I hope this makes sense, but a rule at DB level such as the one you are defining is extremely limiting..

bobleujr
  • 1,179
  • 1
  • 8
  • 23
  • Hi! Yes it's a very specific constraint, NULL's are allowed, the data simply doesn't exist in some cases. Though where it does, there cannot be repetition. Suppose I got the wrong idea from this Django change? https://github.com/django/django/commit/49ef21d9ea - anyway going to override the save method like you suggested, and fall back to some unique_together constraint otherwise. Thank you! – Rosie Feb 14 '17 at 19:42
  • Definitely, you could use a unique_together with a non-unique field (and your primary key) to create a 2-field unique tuple! – bobleujr Feb 14 '17 at 19:45
  • @bobleujr it's a fairly common scenario actually, the problem here is not that "this doesn't makes sense", it makes perfect sense. The problem is on MS SQL Server's side, as Jamie points out in another answer. – Sebastián Vansteenkiste Sep 06 '19 at 15:01