duplicate key value violates unique constraint with AutoField PK

Peyo · October 12, 2020, 9:08pm

I have strange issue with which I am banging my head off the wall for days now.
I have a model as such:

class historical_recent_data(models.Model):
    id = models.AutoField(primary_key=True)
    reference = models.CharField(max_length=100, verbose_name= 'reference')
    Date = models.DateField()
    Quantity = models.FloatField(default=0)
    NetAmount = models.FloatField(default=0)
    
    def __str__(self):
        return self.reference

and the view is like this:

def New_Sales(request):
    #context = {}
    form = modelformset_factory(historical_recent_data, fields=('id','reference', 'Date','Quantity', 'NetAmount'))
    
    
    if request.method == 'GET':
        formset = form(queryset= historical_recent_data.objects.none())
    elif request.method == 'POST':
        formset = form(request.POST)
        if formset.is_valid():
            
            
            
            
            for check_form in formset:
                check_form.save()
            
                quantity = check_form.cleaned_data.get('Quantity')
                id = check_form.cleaned_data.get('reference')
                update = replenishment.objects.filter(Id = id).update(StockOnHand = F('StockOnHand') - quantity)
                update2 = Item2.objects.filter(reference = id).update(stock_reel = F('stock_reel') - quantity)
                
            
            
        
            return redirect('/dash2.html') 
    

    return render(request, 'new_sale.html', {'formset':formset})

and here is how I render the formset in template:

<section class="no-padding-top no-padding-bottom">
          <div class="container-fluid">
              <div class="d-sm-flex align-items-center justify-content-between mb-4">
                    <h1 class="h3 mb-0 text-gray-800">{% trans 'RECORD NEW SALE' %}</h1>
                    
                </div>
              <form method="POST" class="form-validate" id="form_set">
		      {% csrf_token %}
 
                 {{ formset.management_form }}
                  <table id="formset" class="form"> 
 {% for form in formset.forms %}
   {% if forloop.first %}
   <thead><tr> 
     {% for field in form.visible_fields %} 
     <th>{{ field.label|capfirst }}</th> 
    {% endfor %}
   </tr></thead> 
   {% endif %}
   <tr class="{% cycle row1 row2 %}">
   {% for field in form.visible_fields %} 
    <td>
    {# Include the hidden fields in the form #}
    {% if forloop.first %}
     {% for hidden in form.hidden_fields %}
      {{ hidden }}
     {% endfor %}
     {% endif %}
      {{ field.errors.as_ul }}
       {{ field }}
      
     </td> 
   {% endfor %} 
   </tr>
 {% endfor %}
     
                   
 </table> 
		 
<div id="form_set" style="display:none">
    <table class='no_error'>
      {{ form }}
     </table>
 </div>

         </div>
          <br>
          <br>
		  </form>
<button class="btn btn-success" id="add_more">Add</button>		  
<button class="btn btn-primary" type="submit" form="form_set">Save</button> 
		  
        </section>

and when I try to create a model instance with a form in template, I get this error:

IntegrityError at /new_sale.html
duplicate key value violates unique constraint "dashboard2_historical_recent_data_pkey"
DETAIL:  Key (id)=(3) already exists.

I know that id is a django automatic field, I tried without it too, but I get the same output everytime. In my database, I have about 10000 rows, why would the id autofield would not increment starting at 10000?

KenWhitesell · October 12, 2020, 10:11pm

Can you confirm that the error being thrown is being thrown on the line check_form.save()? If not, what line is throwing the error?

I don’t see where you’re defining a field named Id anywhere, but you’re referencing it in your modelformset_factory - where’s that coming from?

Also, you don’t need to save each form individually. A formset supports the .save method on the formset itself.

Peyo · October 13, 2020, 6:05am

Hi Ken, I updated my post to correct this id issue, it was a typo for this example. As far as the error, it indeed happens at formset_check.save() line. By tweaking id field datatype in postgresql, I can now upload several forms at once, but only one form per ‘reference’ get saved. How strange!

KenWhitesell · October 13, 2020, 6:25am

How are you rendering the form set? (Can you show the template?) One common mistake is not rendering all the hidden fields that are part of the form set.

Also, did you change your view to save the formset rather than the individual forms? (Saving the formset properly handles unused forms in the formset.)

Peyo · October 13, 2020, 11:43am

Hi Kent, thanks for your advices, I updated my post to show how the formset is being rendered in template.
I have also tried saving the formset on its own in the view as such:

def New_Sales(request):
    #context = {}
    form = modelformset_factory(historical_recent_data, fields=('id','Id', 'Date','Quantity', 'NetAmount'))
    
    
    if request.method == 'GET':
        formset = form(queryset= historical_recent_data.objects.none())
    elif request.method == 'POST':
        formset = form(request.POST)
        if formset.is_valid():
            
            
            
            
            for check_form in formset:
                #check_form.save()
            
                quantity = check_form.cleaned_data.get('Quantity')
                id = check_form.cleaned_data.get('reference')
                update = replenishment.objects.filter(Id = id).update(StockOnHand = F('StockOnHand') - quantity)
                update2 = Item2.objects.filter(reference = id).update(stock_reel = F('stock_reel') - quantity)
                
            
            formset.save()
        
            return redirect('/dash2.html') 
    
    #else:
        #form = form(queryset= historical_recent_data.objects.none())
        
    
    
    return render(request, 'new_sale.html', {'formset':formset})

but this only saves the last form inside of the formset. I am not sure if I understood your advice properly. Thanks!

KenWhitesell · October 13, 2020, 2:57pm

You’ve reintroduced the reference to the ‘Id’ field in addition to the ‘id’ field, with no corresponding element in the form / model.

Peyo · October 13, 2020, 7:41pm

sorry it was an error, I have tried so many versions, I miss copied it. Anyways, things are still not working with this process, at this point I don’t know what else to try out.

I just noticed that clicking “add” to add a form in the template, create the number of formset already existing on the template. For instance, if there are 2 forms on the formset, clicking on “add” would create 2 new forms. Maybe this javascript code is causing the problem?

<script type='text/javascript'>

$('#add_more').click(function() {
    var form_idx = $('#id_form-TOTAL_FORMS').val();
    $('#form_set').append($('#form_set').html().replace(/__prefix__/g, form_idx));
    $('#id_form-TOTAL_FORMS').val(parseInt(form_idx) + 1);
});

</script>

KenWhitesell · October 14, 2020, 1:11am

Yep, this is the cause of the problem.

There’s a function in formsets to render a blank form. What I do is render an instance of the blank form in a hidden div, and then make a copy of it to add to my form.

Peyo · October 14, 2020, 5:54am

Thanks for all your help Ken, I think I am a bit over my head here ^^. Following your advice I made this to have a hidden form in my template.

			      <table class="no_error">
				      {{ form }}
			      </table>
		      </div>

and this javascript code to show the form on the click of the “add” button,

<script type='text/javascript'>
  $('#add_more').click(function () {
	  $('#hidden').show();
   
</script>

but it unfortunately do not work. Would it be the correct approach though?

Peyo · October 14, 2020, 6:21am

last night I almost figured this out doing this:

<section class="no-padding-top no-padding-bottom">
          <div class="container-fluid">
              <div class="d-sm-flex align-items-center justify-content-between mb-4">
                    <h1 class="h3 mb-0 text-gray-800">{% trans 'RECORD NEW SALE' %}</h1>
                    
                </div>
              <form method="POST" class="form-validate" id="formset">
		      {% csrf_token %}
 
                 {{ formset.management_form }}
                  <table id="formset" class="form"> 
 {% for form in formset.forms %}
   {% if forloop.first %}
   <thead><tr> 
     {% for field in form.visible_fields %} 
     <th>{{ field.label|capfirst }}</th> 
    {% endfor %}
   </tr></thead> 
   {% endif %}
   <tr class="{% cycle row1 row2 %}">
   {% for field in form.visible_fields %} 
    <td>
    {# Include the hidden fields in the form #}
    {% if forloop.first %}
     {% for hidden in form.hidden_fields %}
      {{ hidden }}
     {% endfor %}
     {% endif %}
      {{ field.errors.as_ul }}
       {{ field }}
      
     </td> 
   {% endfor %} 
   </tr>
 {% endfor %}
     
                   
 </table> 
		 
 <div id="emptyform_wrapper" style="display:none"> 
   <table class='no_error'> 
      {{ form }}
     </table> 
 </div>
				      

         </div>
          <br>
          <br>
		  </form>
<button class="btn btn-success" id="add_more">Add</button>		  
<button class="btn btn-primary" type="submit" form="formset">Save</button> 
		  
        </section>
  
<script type='text/javascript'>
  $('#add_more').click(function () {
    var form_idx = $('#id_form-TOTAL_FORMS').val();
    $('#formset').append($('#emptyform_wrapper').html().replace(/__prefix__/g, form_idx));
    $('#id_form-TOTAL_FORMS').val(parseInt(form_idx) + 1);
  });

</script>

the only issue with this code is that the add button adds a form but it never gets displayed so the user needs to try saving to get django to show the “added” form saying that they cannot. be empty. Then saving and all works fine. Getting closer!

KenWhitesell · October 14, 2020, 6:25am

Not quite it.

(Can’t find any good examples at the moment unfortunately.)

Basically, in the view (winging it here, there are likely to be errors):

blank_form = formset.empty_form
render(request, template, {'formset': formset, 'blank': blank_form})

Somewhere in the template:

<div style="display: none;">
<div id="blank-row">{{blank}}</div>
</div>

In your JavaScript (in this case, uses jQuery, and this example is mangled from some code I have):

$('#add_row').click(function() {
    var form_idx = $('#id_formset-TOTAL_FORMS').val();
    $('#formset tr:last').after(
        $('#blank-row')[0].outerHTML.replace(/__prefix__/g, form_idx)
    )
    $('#id_formset-TOTAL_FORMS').val(parseInt(form_idx) + 1);
})

(This snippet comes from a really strange chunk of code, I wouldn’t necessarily do it this way again, but it worked for what we needed it to do at the time. Not that it really matters, but I believe this was for a case where we had multiple formsets on the same page, so we put the blank row at the end of the table. Anyway, it should give you an idea of what needs to be done.)

Ken

Peyo · October 14, 2020, 11:26pm

thank you very much Ken, I hope I will figure it out!

Topic		Replies	Views
Autofield pk generates null value violation in save? Using Django	3	954	May 23, 2021
updating formsets with FormView CBV Forms & APIs	8	786	September 2, 2023
Django.db.utils.IntegrityError: duplicate key value violates unique key constraint.... Using the ORM	2	13718	August 26, 2022
UniqueConstraint messing with looped updates/creation Using the ORM	4	506	December 27, 2022
Django IntegrityError: Unique Constraint Violation Mystery Errors	2	77	November 8, 2024

duplicate key value violates unique constraint with AutoField PK

Related topics