0

I am trying to remove a reference column from my database, locally using sqlite3.

Was getting the SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "features" so I tried removing the foreign key first. That did not change anything in my schema file, other than the version because a migration was successfully run.

Now trying 

def up
  remove_index :features, column: [:user_id]
end

def down
  add_index :features, column: [:user_id]
end

And this replaces t.index ["user_id"], name: "index_features_on_user_id" with t.index [nil], name: "index_features_on_user"

I tried playing with the remove_index to be just :user or :user_id as the second parameter, but no luck.

I even tried to add a name and the same nil remove_index :features, :user_id, name: :index_features_on_user

Ultimately I want to remove the entire reference to user, the t.index and the t.integer "user_id", but have not been successful and thought it was because there was that index sitting there refusing to be deleted, but I could be wrong.

UPDATE

Here is the features table 

  create_table "features", force: :cascade do |t|
    t.integer "instance_id"
    t.integer "user_id"
    t.integer "experiment_id"
    t.string "name"
    t.text "values"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.boolean "is_allow_na", default: false, null: false
    t.index ["experiment_id"], name: "index_features_on_experiment_id"
    t.index ["instance_id"], name: "index_features_on_instance_id"
    t.index ["user_id"], name: "index_features_on_user_id"
  end
madav
  • 2,918
  • 1
  • 13
  • 17
  • can you give more information about the model of the table you are dropping ? – Abdullah Fadhel Apr 27 '20 at 09:44
  • is there another tables that have foreign keys referencing the table you are trying to drop? – Abdullah Fadhel Apr 27 '20 at 09:46
  • It was created with `t.belongs_to :user` I tried dropping FK's, migration was successful but nothing changed in the schema. – madav Apr 27 '20 at 09:51
  • All I want to do is reverse the t.belongs_to column and whatever it created – madav Apr 27 '20 at 10:05
  • how does your to tables look like can I see their columns in the schema.rb, plus the content of both models – Abdullah Fadhel Apr 27 '20 at 10:07
  • this migration can be reversed by rails it self it will now how to reverse it since it is not something complex that it can not reverse. however your problem could be with the data residing in your table so a look at your models and tables columns could help. – Abdullah Fadhel Apr 27 '20 at 10:11
  • Thanks Abdullah, updated what the table looks like in the schema. I appreciate your help – madav Apr 27 '20 at 10:15
  • does your feature model have has_many relations with other models ? – Abdullah Fadhel Apr 27 '20 at 10:17
  • a possible cause of the problem that there might be another table having `feature_id` reference in it to the feature table so the foreign key in that table is not allowing the drop of features table. – Abdullah Fadhel Apr 27 '20 at 10:21
  • The feature model has has relationships with experiments, instances, and yes the feature_id belongs to other tables. But I can't be expected to remove all that just for one simple column deletion? Does that mean the reference is there forever now? – madav Apr 27 '20 at 10:29
  • since there is a `feature_id` column in other tables that means the feature table is a parent table to them. and the foreign key on the `feature_id` on the other table is what is causing this error. since you need to move this table make sure you remove any foreign keys on the tables that are referencing `features` table. actually since the table is going to be dropped then there is no meaning for the `feature_id` column to exist on them anymore since it has no benefit to keep it after dropping the table. so you have to remove it from all tables in in the migrations before you drop your table. – Abdullah Fadhel Apr 27 '20 at 10:52
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/212609/discussion-between-madav-and-abdullah-fadhel). – madav Apr 27 '20 at 10:53
  • removing the foreign key from the other tables referencing the feature table will do the trick. but as I said you better delete the column since it will no longer be useful. – Abdullah Fadhel Apr 27 '20 at 10:53

0 Answers0