CharField ForeignKey forced to int during lookup in get_prep_value in django.db.models.fields

[harijay]

I created a small test case to illustrate the problem.

>>> c = Child.objects.filter(parents_ssn="2354234234")

Suceeds!

>>> print c[0].name
werwer sdfgsdg

The following lookup fails

>>> cfails = Child.objects.filter(parents_ssn="g354234234c")
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/hari/djtrunk/django/db/models/manager.py", line 141, in filter
    return self.get_query_set().filter(*args, **kwargs)
  File "/home/hari/djtrunk/django/db/models/query.py", line 550, in filter
    return self._filter_or_exclude(False, *args, **kwargs)
  File "/home/hari/djtrunk/django/db/models/query.py", line 568, in _filter_or_exclude
    clone.query.add_q(Q(*args, **kwargs))
  File "/home/hari/djtrunk/django/db/models/sql/query.py", line 1170, in add_q
    can_reuse=used_aliases, force_having=force_having)
  File "/home/hari/djtrunk/django/db/models/sql/query.py", line 1105, in add_filter
    connector)
  File "/home/hari/djtrunk/django/db/models/sql/where.py", line 67, in add
    value = obj.prepare(lookup_type, value)
  File "/home/hari/djtrunk/django/db/models/sql/where.py", line 316, in prepare
    return self.field.get_prep_lookup(lookup_type, value)
  File "/home/hari/djtrunk/django/db/models/fields/related.py", line 136, in get_prep_lookup
    return self._pk_trace(value, 'get_prep_lookup', lookup_type)
  File "/home/hari/djtrunk/django/db/models/fields/related.py", line 209, in _pk_trace
    v = getattr(field, prep_func)(lookup_type, v, **kwargs)
  File "/home/hari/djtrunk/django/db/models/fields/__init__.py", line 882, in get_prep_lookup
    return super(IntegerField, self).get_prep_lookup(lookup_type, value)
  File "/home/hari/djtrunk/django/db/models/fields/__init__.py", line 292, in get_prep_lookup
    return self.get_prep_value(value)
  File "/home/hari/djtrunk/django/db/models/fields/__init__.py", line 876, in get_prep_value
    return int(value)
ValueError: invalid literal for int() with base 10: 'g354234234c'


################
models.py has:
################

from django.db import models

class Parent(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=384, blank=True)
    ssn = models.CharField(max_length=768, blank=True)
    class Meta:
        db_table = u'Parent'
        app_label = u'mydjapp'

class Child(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=384, blank=True)
    parents_ssn = models.ForeignKey(Parent, null=True, db_column='parents_ssn', blank=True)
    class Meta:
        db_table = u'child'
        app_label= u'mydjapp'

~                                       
#####################
The database columns are:
######################

mysql> select * from Parent;
+----+--------------+--------------+
| id | name         | ssn          |
+----+--------------+--------------+
|  1 | Aefwesk baob | 42s354234234 |
|  2 | Ask bob      | 2354234234   |
|  3 | Seelan Cyata | 2354234234c  |
|  4 | Hdel Abnot   | g354234234c  |
+----+--------------+--------------+
4 rows in set (0.00 sec)


mysql> select * from child;
+----+----------------+-------------+
| id | name           | parents_ssn |
+----+----------------+-------------+
|  1 | werwer sdfgsdg | 2354234234  |
|  2 | Hyadr Abnot    | g354234234c |
+----+----------------+-------------+
2 rows in set (0.00 sec)



##########################
The raw SQL test was setup :
#######################
CREATE TABLE `Parent` (

  `id` int(11) NOT NULL,
  `name` varchar(128) DEFAULT NULL,
  `ssn` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),

  KEY `parents_ssn_fk` (`ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


CREATE TABLE `child` (

  `id` int(11) NOT NULL,
  `name` varchar(128) DEFAULT NULL,
  `parents_ssn` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),

  KEY `parents_ssn` (`parents_ssn`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parents_ssn`) REFERENCES `Parent` (`ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

[Karen Tracey]

The cause of the exception you are seeing is that per your Django models, the target column of the ForeignKey field in your Child model is the primary key field of Parent, not the ssn field. Primary key field of parent is an integer, so attempting to lookup a non-integer value raises an exception.

In order to tell Django that the target column for the ForeignKey field in child is the parent's ssn field, you need to specify to_field='ssn' on that ForeignKey definition. See: ​http://docs.djangoproject.com/en/1.2/ref/models/fields/#django.db.models.ForeignKey.to_field

Note though that your existing table definitions don't meet the requirements for a ForeignKey field here, because the Parent ssn field is not unique. Django's ForeignKey is a many-to-one relation, so the target column must be unique. See #11702. If you are not actually creating the tables via syncdb you may not initially see any error related to this failure to meet the requirements for a ForeignKey -- but if in fact you have duplicated values in that target field, you may see errors later on.

Your Django models also show evidence of bug #5725. The max_length values for all your CharFields are 3x higher than they should be. Easiest fix is to manually correct them to be the right value.