user2558053 user2558053 - 5 months ago 30
SQL Question

django.db.utils.IntegrityError: (1062, "Duplicate entry for user_id")

I have a certain model:

class Problem(models.Model):

title = models.CharField(max_length=80)
problem_text = models.TextField(max_length=5000)
pub_date = models.DateTimeField(default=timezone.now)
votes = models.IntegerField(default=0)
wiki_answer = models.TextField(max_length=5000, null=False, default='', blank=True)
topic = models.CharField(max_length=50, blank=False)
position = models.CharField(max_length=50, null=True, blank=False)
user = models.OneToOneField(User, null=True, blank=False, unique=False)


And after adding more than one post written by one user Django raises an error even though a user is not supposed to be unique. Here is the full stack trace:

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/backends/utils.py" in execute
64. return self.cursor.execute(sql, params)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/backends/mysql/base.py" in execute
112. return self.cursor.execute(query, args)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/cursors.py" in execute
226. self.errorhandler(self, exc, value)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/connections.py" in defaulterrorhandler
36. raise errorvalue

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/cursors.py" in execute
217. res = self._query(query)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/cursors.py" in _query
378. rowcount = self._do_query(q)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/cursors.py" in _do_query
341. db.query(q)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/connections.py" in query
280. _mysql.connection.query(self, query)

The above exception ((1062, "Duplicate entry '1' for key 'interview_questions_problem_user_id_105dd8d0_uniq'")) was the direct cause of the following exception:

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/core/handlers/base.py" in get_response
149. response = self.process_exception_by_middleware(e, request)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/core/handlers/base.py" in get_response
147. response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/views/generic/base.py" in view
68. return self.dispatch(request, *args, **kwargs)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/views/generic/base.py" in dispatch
88. return handler(request, *args, **kwargs)

File "/Users/artemdremov/Documents/python/interview_project/interview_questions/views.py" in post
55. post.save()

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/models/base.py" in save
708. force_update=force_update, update_fields=update_fields)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/models/base.py" in save_base
736. updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/models/base.py" in _save_table
820. result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/models/base.py" in _do_insert
859. using=using, raw=raw)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/models/manager.py" in manager_method
122. return getattr(self.get_queryset(), name)(*args, **kwargs)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/models/query.py" in _insert
1039. return query.get_compiler(using=using).execute_sql(return_id)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/models/sql/compiler.py" in execute_sql
1060. cursor.execute(sql, params)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/backends/utils.py" in execute
79. return super(CursorDebugWrapper, self).execute(sql, params)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/backends/utils.py" in execute
64. return self.cursor.execute(sql, params)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/utils.py" in __exit__
95. six.reraise(dj_exc_type, dj_exc_value, traceback)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/utils/six.py" in reraise
685. raise value.with_traceback(tb)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/backends/utils.py" in execute
64. return self.cursor.execute(sql, params)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/django/db/backends/mysql/base.py" in execute
112. return self.cursor.execute(query, args)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/cursors.py" in execute
226. self.errorhandler(self, exc, value)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/connections.py" in defaulterrorhandler
36. raise errorvalue

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/cursors.py" in execute
217. res = self._query(query)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/cursors.py" in _query
378. rowcount = self._do_query(q)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/cursors.py" in _do_query
341. db.query(q)

File "/Users/artemdremov/Documents/python/interview_project/venv/interview_project/lib/python3.4/site-packages/mysqlclient-1.3.7-py3.4-macosx-10.6-intel.egg/MySQLdb/connections.py" in query
280. _mysql.connection.query(self, query)

Exception Type: IntegrityError at /p/add/
Exception Value: (1062, "Duplicate entry '1' for key 'interview_questions_problem_user_id_105dd8d0_uniq'")


Django version 1.9.5.

Here is the view for adding post form:

class AddView(View):
template_name = 'interview_questions/add.html'
form_class = ProblemForm
success_url = '/'

# display blank form
def get(self, request):
if request.user.is_authenticated():
form = self.form_class(None)
return render(request, self.template_name, {'form': form, 'user': request.user})
else:
return redirect('interview_questions:index')

# process data
def post(self, request):
form = self.form_class(request.POST)

if form.is_valid() and request.user.is_authenticated():
post = form.save(commit=False)
post.pub_date = timezone.now()
post.user = request.user
post.save()
return redirect('interview_questions:index')

return render(request, self.template_name, {'form': form, 'user': request.user})

Answer

The error happens here:

"/Users/artemdremov/Documents/python/interview_project/interview_questions/views.py" in post
  55.             post.save()

And that's because you have this in your model.

user = models.OneToOneField(User, null=True, blank=False, unique=False)

unique=False has no effect here because there can be exactly one entry here.

OneToOneField

A one-to-one relationship. Conceptually, this is similar to a ForeignKey with unique=True, but the “reverse” side of the relation will directly return a single object.

There are three solutions

  1. Change your OneToOneField to a ForeignKey
  2. In your form validation make sure that a record does not exist
  3. When creating the form check if an entry already exists and then allo the user to edit it.