ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table

Question

I am new to Rails, and trying to make a simple web app.

I generated the following components:

rails generate scaffold user username:string password:string

rails generate scaffold appointment doctor:references patient:references

and in the appointment model I specified the class for doctor and patient to be users like below

# app/models/appointment.rb class Appointment < ApplicationRecord belongs_to :doctor, :class_name => "User" belongs_to :patient, :class_name => "User" end

I left everything else to be the same, by applying rails db:migrate and rake db:test:prepare, I got this error message saying I don't have the table doctor

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table: main.doctors

But I thought the specification in appointment.rb would do the job for me.

How do I make this work?

Mosaaleb · Accepted Answer · 2020-03-07 23:15:36Z

doctor: references and patient: references will create two columns in the appointments table with doctor_id and patient_id which will reference doctors table and patients table respectively.

No surprise here, these tables don't exist. To tell the migration that doctor and patient are actually from users table you need to use to_table in your migration:

Open the appointments migration file in db/migrate/ and add to_table option for both patient references and doctor references:

t.references :doctor, foreign_key: { to_table: :users }, index: true t.references :patient, foreign_key: { to_tabale: :users }, index: true

to_table is used when you want to make a column reference a different table.

Clear answer, and with the clearer to_table use for specifying the foreign key. (Small typo in the answer, "to_tabale" -> "to_table".)

bliof · Accepted Answer · 2020-03-07 21:30:08Z

The scaffold most probably generated a migration with a foreign_key: true on the references.

Check create_appointments migration file in db/migrate/ and remove it.

If you want to have a foreign key add:

add_foreign_key :appointments, :users, column: :doctor_id add_foreign_key :appointments, :users, column: :patient_id

https://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/add_foreign_key

The foreign key constraint checks if the id that you are setting is present in the other table. If it is not, it will crash when you create a record.

can you tell me more about removing the foreign_key: true? they are indeed foreign_keys, aren't they?
The migration files describe changes to your database. The foreign_key: true tells it to add a standard foreign key constraint -> google it ;) w3schools.com/sql/sql_foreignkey.asp By default it will use the column name a.k.a. doctor_id and will try to guess the table -> doctors. To tell it to add the constraint against a different table 👀 at the code in the answer

Collectives™ on Stack Overflow

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related