save() method deletes existing record when using unique_together in Django model

Question

I am using the following class in models.py:

class Player(models.Model):
    id = models.AutoField(primary_key=True)
    sport = models.CharField(max_length=3, choices=SPORT_CHOICES)
    yid = models.IntegerField()
    first = models.CharField(max_length=30)
    last = models.CharField(max_length=30)
    team = models.CharField(max_length=3)
    class Meta:
        unique_together = (("sport", "yid"),)

If the following code is run and a player with that sport/yid already exists in the database, then the record for that player is deleted and the new player record is inserted:

for (sport, yid, first, last, team) in NBA_PLAYERS:
    player = Player(sport=sport, yid=yid, first=first, last=last, team=team)
    player.save()

The major problem with this is that the autogenerated id of the new record is different than the id in the deleted record and other classes that use Player as a foreign key referenced that record with the old id.

SQL calls seem to work correctly:

mysql> insert into swap_player (sport, yid, first, last, team) values ('mlb', 1234, 'Joel', 'Zumaya', 'det');
ERROR 1062 (23000): Duplicate entry 'mlb-1234' for key 2

Here is the CREATE TABLE call from Django:

CREATE TABLE `swap_player` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `sport` varchar(3) NOT NULL,
    `yid` integer NOT NULL,
    `first` varchar(30) NOT NULL,
    `last` varchar(30) NOT NULL,
    `team` varchar(3) NOT NULL,
    UNIQUE (`sport`, `yid`)
)
;

The error is missing only if the save() function is kicked off by an action on a web page (HttpRequest). If I run the save() function from the python command line, then I get the correct error:

>>>
>>> p = Player(sport='mlb', yid=1234, first='Joel', last='Zumaya', team='det')
>>> p.save()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/dave/Development/django_trunk/django/db/models/base.py", line 328, in save
    self.save_base(force_insert=force_insert, force_update=force_update)
  File "/Users/dave/Development/django_trunk/django/db/models/base.py", line 400, in save_base
    result = manager._insert(values, return_id=update_pk)
  File "/Users/dave/Development/django_trunk/django/db/models/manager.py", line 138, in _insert
    return insert_query(self.model, values, **kwargs)
  File "/Users/dave/Development/django_trunk/django/db/models/query.py", line 894, in insert_query
    return query.execute_sql(return_id)
  File "/Users/dave/Development/django_trunk/django/db/models/sql/subqueries.py", line 309, in execute_sql
    cursor = super(InsertQuery, self).execute_sql(None)
  File "/Users/dave/Development/django_trunk/django/db/models/sql/query.py", line 1756, in execute_sql
    cursor.execute(sql, params)
  File "/Users/dave/Development/django_trunk/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "/Users/dave/Development/django_trunk/django/db/backends/mysql/base.py", line 83, in execute
    return self.cursor.execute(query, args)
  File "build/bdist.macosx-10.5-i386/egg/MySQLdb/cursors.py", line 166, in execute
  File "build/bdist.macosx-10.5-i386/egg/MySQLdb/connections.py", line 35, in defaulterrorhandler
_mysql_exceptions.IntegrityError: (1062, "Duplicate entry 'mlb-1234' for key 2")
>>> connection.queries
[{'time': '0.000', 'sql': u'INSERT INTO `swap_player` (`sport`, `yid`, `first`, `last`, `team`) VALUES (mlb, 1234, Joel, Zumaya, det)'}]
>>> 
>>>

How can I get the save() method to error out if a record with the same sport/yid already exists?

Answer 1

You could overwrite the Player:save() method. with something like as follows. I'm not versed in the properties of unique_together to know if what you are experiencing indicates a bug though.

def save(self, *args, **kwargs):
    try:
        Player.objects.get(yid = self.yid, sport=self.sport)
        raise PlayerObjectExists
    except:
        return super(Player, self).save(*args, **kwargs)

Answer 2

Sounds like you need to take a look at the SQL the django orm is creating.