Sadia Naz Sadia Naz - 2 years ago 100
Ruby Question

Mysql2::Error: Duplicate entry '2016' for key 'PRIMARY' using Rails

I have tried almost every answer available on this forum but nothing helped.

All my students have tracking IDs in this format:

2016-N-XXXX(any four digit number)

I have an "add student form" which is giving this error whenever I try to add a student with ID in this format.

My "create student" migration file is:

class CreateStudents < ActiveRecord::Migration
def up
create_table :students do |t|
t.string "name"
t.string "tracking_id"
t.float "matric_percentage"
t.integer "monthly_income"
t.column "SEX", 'CHAR(1)'
t.string "section" , index: true, foreign_key: true
t.string "city"
t.string "father_name"
t.string "DOB"
t.string "email"
t.string "phone_number"
t.string "secondary_phone_number"
t.string "mailing_address"
t.string "username"
t.string "password_digest"
t.timestamps null: false
def down
drop_table :students

My model student.rb looks like:

class Student < ActiveRecord::Base
belongs_to :sections
def self.authenticate(user,pass)
student = Student.find_by_username(user)
if student.authenticate(pass)
return student[:id]
return false

Here is my students_controller.rb (only relevant functions):

class StudentsController < ApplicationController
def new
@student =
def create
@student =[:tracking_id]
respond_to do |format|
flash[:notice] = 'Student was successfully created.'
format.html { redirect_to users_path(:admin => "students_view")}
format.json { render :show, status: :created, location: @student }
format.html { render :new }
format.json { render json: @student.errors, status: :unprocessable_entity }
def student_params
params.require(:student).permit( :name, :tracking_id, :matric_percentage, :monthly_income, :SEX, :section,:city, :father_name, :DOB, :email, :phone_number, :secondary_phone_number, :mailing_address, :username, :password)

Here is my form view:

<%= bootstrap_form_for do |f| %>
<%= f.errors_on :name %>
<%= f.text_field :name, label: "Name", :required => true%>
<%= f.text_field :tracking_id, label: 'NOP Tracking ID', :required => true %>
<!--<%= f.text_field :section, label: "Section", :required => true %>-->
<%= :section, [["Select:", "?"],["Section A", "A"], ["Section B", "B"],["Section C", "C"],["Section D", "D"],["Section E", "E"],["Section F", "F"],["Section G", "G"], ["Section H", "H"], ["Section I", "I"], ["Section J", "J"], ["Section K", "K"], ["Section L", "L"]], { label: "Section" },{ class: "selectpicker" } %>
<%= f.text_field :father_name, label: 'Father Name', :required => true %>
<%= f.text_field :DOB, label: 'Date of Birth', :required => true, help: "Be Careful! Enter date in dd/mm/yyyy pattern only" %>
<%= :SEX,[["Select:", "?"],["Male", "M"], ["Female", "F"]],{ label: "Gender" },{ class: "selectpicker" } %>
<%= f.text_field :matric_percentage, label: "Matric Percentage", :required => true %>
<%= f.number_field :monthly_income, label: "Monthly income", :required => true %>
<%= f.text_field :city, label: 'City' %>
<%= f.email_field :email, label: 'Email Address' %>
<%= f.text_field :phone_number, label: 'Phone Number', :required => true %>
<%= f.text_field :secondary_phone_number, label: 'Secondary Phone Number', :required => true %>
<%= f.text_field :mailing_address, label: 'Mailing Address' %>
<%= f.text_field :username, label: 'Username', :required => true %>
<%= f.text_field :password, label: 'Password', :required => true %>
<div class="clearfix" type = "submit">
<%= f.submit %>
<% end %>

And the complete error statement:

Mysql2::Error: Duplicate entry '2016' for key 'PRIMARY': INSERT INTO `students` (`name`, `tracking_id`, `matric_percentage`, `monthly_income`, `SEX`, `section`, `city`, `father_name`, `DOB`, `email`, `phone_number`, `secondary_phone_number`, `mailing_address`, `username`, `password_digest`, `id`, `created_at`, `updated_at`) VALUES ('Sohail Aslam', '2016-N-7865', 344.0, 455, 'F', 'C', 'Lahore', 'Aslam', '25/12/1995', '', '+923229499463', '+923229499463', 'Lahore', 'student', '$2a$10$SZAV08mASBTM8oQr1.P.IOIKRD.Acn/F/VfBDI0DS2.n4cVuWO9B2', 2016, '2016-04-07 18:54:50', '2016-04-07 18:54:50')

Answer Source

The Problem is the line[:tracking_id]

The id is not the same as tracking_id. While id is the Primary Key (PK) of your table (added automatically through the migration) tracking_id is an additional key that was added by you.

You should keep the standard PK and let the DB set its value (usually done through sequences). Since you assign the tracking id that the user entered in the form to the id property, you are telling Rails that you take care of the PK. Because it is a String, it is converted to integer. See what happens when you convert "2016-1-1234" to int:

puts "2016-1-1234".to_i # => 2016

Now once you submitted the form there is a student with PK 2016 in the DB. And the next user is also assigned the same PK value. Hence the DB will complain, that there is already a row with the same PK.

Some remarks on your code:

  • I'd keep the column names all lowercase (DOB, SEX). Just for consistency.
  • SEX column can be created with t.string :sec, limit: 1
  • You should add a unique index to your tracking_id: add_index(:students, :tracking_id, unique: true)
  • You probably have created the controller from scaffold? Remove the respond_to, and format.json as well as format.html, the content of format.html block is actually enough (unless you have a JSON API)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download