Rails - Foreign Key Constraint Failure For Belongs To-Has Many Relationship

Question

class Appointment < ApplicationRecord
  belongs_to :teacher
  belongs_to :student
end

class Teacher < User
    has_many :appointments
end

class Student < User
    has_many :appointments
end

class User < ApplicationRecord
    has_secure_password

    validates :username, presence: true
    validates :username, uniqueness: true
    validates :username, length: { minimum: 4 }
    validates :email, presence: true
    validates :email, uniqueness: true
end

Schema:

ActiveRecord::Schema[7.0].define(version: 2024_06_06_180259) do
  create_table "appointments", force: :cascade do |t|
    t.datetime "start_datetime"
    t.integer "teacher_id", null: false
    t.integer "student_id", null: false
    t.string "notes"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "status"
    t.index ["teacher_id"], name: "index_appointments_on_teacher_id"
    t.index ["student_id"], name: "index_appointments_on_student_id"
  end

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

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

  create_table "users", force: :cascade do |t|
    t.string "email"
    t.string "username"
    t.string "password_digest"
    t.string "first_name"
    t.string "last_name"
    t.string "type"
    t.string "phone_number"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  add_foreign_key "appointments", "teachers"
  add_foreign_key "appointments", "students"
end

I was previously having problems with using STI to represent Teachers and Students in the same Users db table. But now I've made separate tables for Teachers and Students. Still, I'm getting the foreign key constraint failed error. I'm not sure what I'm doing wrong here. It looks like I did my associations correctly. Can someone guide me here?

Answer 1

I think this may have been an issue with SQLite, but I will need to confirm first on MySQL/Postgres. For now, the fix was re-running the migration to create the appointments table using the below migration code that was able to successfully create the association between appointments and students & teachers. The important change is adding to the references foreign_key: { to_table: :users } to formally link the users table. Up until this point, no other suggestion was working but this fixed the issue. I will update when I have time to check against MySQL.

class CreateAppointments < ActiveRecord::Migration[7.0]
  def change
    create_table :appointments do |t|
      t.datetime :start_datetime
      t.references :teacher, null: false, foreign_key: { to_table: :users }
      t.references :student, null: true, foreign_key: { to_table: :users }
      t.string :status

      t.timestamps
    end
  end
end