Foreign key constraint failed error

rubyonrails-talk
1

Hello everyone!

I am getting invalid foreign key error in my controllers create method, I think my associations and controller is set up properly. I can share the full repo if needed, I was almost going to recreate the tyt model(you can think it as exam) and associations and start it over but I thought would be better if I understand the cause of the error. Since I am quite a beginner problem might be super simple, any idea/ direction would be appreciated, Thank you!


ActiveRecord::InvalidForeignKey in TytsController#create
SQLite3::ConstraintException: FOREIGN KEY constraint failed
app/controllers/tyts_controller.rb:7:in `create' 

class TytsController < ApplicationController

  def create
    if current_user.type == 'Student'
      @tyt = Tyt.new(tyt_params)
      @tyt.student_id = current_user.id
      @tyt.save
      redirect_to root_path
    else
      redirect_to root_path
    end
  end

  private

    def tyt_params
        params.permit(
          :student_id,
          :turkish_correct, 
          :turkish_incorrect, 
          :math_correct, 
          .
          .
          .
          :chemistry_incorrect)
      end
  end

class Student < User
    attr_accessor :teacher_id

    has_many :connections, dependent: :destroy
    has_many :teachers, through: :connections, dependent: :destroy, foreign_key: "teacher_id"
    has_many :tyts, dependent: :destroy, foreign_key: "student_id"

end


class Tyt < ApplicationRecord

  belongs_to :student

end


ActiveRecord::Schema[7.0].define(version: 2023_08_01_092656) do
  

  create_table "students", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "tyts", force: :cascade do |t|
    t.integer "turkish_correct"
    t.integer "turkish_incorrect"
    t.integer "math_correct"
    .
    .
    .
    .
    t.integer "student_id", null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["student_id"], name: "index_tyts_on_student_id"
  end

  create_table "users", force: :cascade do |t|
    t.string "email", default: "", null: false
    t.string "encrypted_password", default: "", null: false
    t.string "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "type"
    t.string "first_name"
    t.string "last_name"
    t.index ["email"], name: "index_users_on_email", unique: true
    t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
  end

  add_foreign_key "tyts", "students"
end
2

Hi there for what its worth I pasted your question into the AI and got this which might be wrong… if it was helpful or correct let me know :slight_smile: cheers.

Thank you for providing more details. Let’s analyze the information.

Database Schema and Model Definition:

The Tyt model has a belongs_to :student association.

The Tyt table has a student_id foreign key column.

The Student model inherits from the User model.

The Student table doesn’t have any columns except timestamps; the actual student data is stored in the users table due to Single Table Inheritance (STI). A foreign key constraint has been added between the tyts table and the students table.

Error Details:

The error is occurring at app/controllers/tyts_controller.rb:7:in ‘create’, which is the line where you are trying to save the Tyt object.

The error message “FOREIGN KEY constraint failed” suggests that the foreign key constraint between the tyts table and the students table is being violated.

Based on the given information, here’s the potential issue:

The Student model is using Single Table Inheritance (STI) with the User model, meaning that both Student and other user types (e.g., Teacher, Admin, etc.) are stored in the users table. However, the foreign key constraint on the tyts table is expecting a valid student_id that exists in the students table, but there’s no such table with unique student IDs. Instead, the student_id should reference the users table.

Solution:

Modify the Foreign Key Constraint:

Remove the existing foreign key constraint from the tyts table that references the students table.

Add a new foreign key constraint to the tyts table that references the users table for the student_id. Here’s how you can modify your migration to reflect this change:

ruby

… other code …

Remove the old foreign key constraint remove_foreign_key “tyts”, “students”

add_foreign_key “tyts”, “users”, column: “student_id”

After making this change, you’ll need to rollback the migration and then re-migrate:

bash

rails db:rollback

rails db:migrate

This should resolve the InvalidForeignKey error, and you should be able to create Tyt objects associated with Student records.

3

Thank you for the answer, yes it is correct! I associate exam and user instead student and it was solved.

It is interesting that I was seeking for help from AI and chat-gpt never gave me this answer :grinning:

4

Hey that is great. I just pasted your forum question I think - here is the chat I had: Debugging Rails `params` Source glad it worked.

5

Interesting, when I paste the exact I don’t get a similar answer InvalidForeignKey Error: Student ID

Do you have chatgptplus perhaps? :thinking:

6

I do. I have gpt 4 in the browser and in emacs with chatgpt-shell I have 3.5 turbo. they are a little different yes. however I also always get different results asking the same thing twice I think. they just assemble the words so they like variations I guess :slight_smile:

7

Yes that makes sense :slightly_smiling_face: , thanks again!

8

before reading the AI-generated answer, I was going to suggest that because tyts requires a value for student_id, this error is occurring because the value of current_user.id does not have a row in the students table. If current_user is not a Student then you have a modeling problem - tyt needs to either reference a User or User needs to reference a Student.

If a tyt really should be related to a student or, to say it another way, a tyt without a student is invalid, then dropping the foreign key constraint is wrong and will result in bad data in your database.

Since Student extends User in code, but there is no relationship to them in the database, you need make a change somewhere.

The AI’s recommendation is unclear - it seems to want you to remove the existing foreign key (which seems like it should not be removed as it seems correct) and then create something that I think will be confusing.

  • If a tyt should always have a student:
    • leave the foreign key
    • add a foreign key from users to students as well as the requisite Active Record configuration
    • in the controller, set student_id to current_user.student.id
    • Perhaps additional validations of a User doesn’t have to be a student
  • if a tyt should instead have a user:
    • change the foreign key from student_id to user_id
1 Like
9

Now I am confused (which is my normal state of course but… ) does the alien not say to “modify” the foreign key by removing it and then adding it again? Is the existing foreign key that you say seems correct not the invalidforeignkey the error is about?

Solution:

Modify the Foreign Key Constraint:

Remove the existing foreign key constraint from the tyts table that references the students table.

Add a new foreign key constraint to the tyts table that references the users table for the student_id.

or is the key there all the time and just the constraints are changed?

10

This part of the AI’s answer doesn’t make sense. What does it mean to “reference the users table for the student id”? I don’t know.

There is an interpretation that could be technically correct, but still not sufficient to actually solve the problem.

It is also possible that the correct solution is to not modify the foreign key constraint at all but instead modify the code or other tables. The AI certainly can’t know nor do we since the OP omitted some key domain knowledge.