Cannot insert duplicate key in object '...'. The duplicate key value is (<NULL>). (2627) (SQLExecDirectW)")

[TheHamz]

Hello there,

Thanks for your effort and for sharing this app with us. I'm struggling to find a solution here. I'm a total nub when it comes to SQL Server... Not really sure if this is a bug or the way SQL Server works. I'm running Django 1.11 and the corresponding django-pyodbc-azure app. SQL server is running on it's latest version. Thanks

class Foo(models.Model):
   name = models.TextField()
   bar = models.ForeignKey('Bar', related_name='+')

class Bar(models.Model):
   name = models.TextField()
   foo = models.ForeignKey('Foo', null=True, related_name='+')

When trying to do the following:

bar = Bar.objects.create(name='bar')
foo = Foo.objects.create(name='foo', bar=bar) <--- getting the error here

[TheHamz]

So, after some digging seems SQL Server doesn't support nullable Foreign Key by default... Well, it does in a way but you can insert NULL only once.. Second insert will fail because NULL value is already present in the column and treated like a duplicate (which will throw an error since the column is tagged as unique). My current workaround is this:

CREATE TABLE dbo.parent(id INT PRIMARY KEY)
CREATE TABLE dbo.child(id INT PRIMARY KEY, parent_id INT NULL, FOREIGN KEY (parent_id) REFERENCES parent(id))

CREATE UNIQUE NONCLUSTERED INDEX yes_it_can_be_nullable
ON dbo.child(parent_id)
WHERE parent_id IS NOT NULL;

insert into dbo.parent values (1),(2),(3),(4),(5)
insert into dbo.child values (1, NULL),(2, NULL),(3, 1),(4, 2),(5, 3)

insert into dbo.child values (6, 1)
-- THIS FAILS AS INTENDED

Hopefully this will help someone who's not experienced with SQL Server (same as me) until we get support for nullable Foreign Key. Any input on this solution is much appreciated. Thanks!

Also, big thanks for sharing this project!

[shaib]

I'm glad you could find this solution. However, note that the problem had to do with uniqueness and nullability, the fact that the column happened to be a Foreign Key had nothing to do with it. You'd get the same problem (and you'll be able to use the same solution) with any field that is null=True, unique=True.