当重命名Users表中的列时，获取:SQLite3 :: ConstraintException:FOREIGN KEY约束失败:DROP TABLE"users" [英] When renaming column in Users table getting: SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "users"

问题描述

我正在使用Sqlite在Rails应用程序上工作，并且将一个users表与其他几个表相关联.尝试在用户"中重命名列时，运行rails db:migrate时出现主题错误.

I am working on a Rails app with Sqlite and have a users table associated with several other tables. When trying to rename a column in Users, I'm getting the subject error when running rails db:migrate.

我在这里看到很多有类似问题的帖子，但没有一个奏效.具体来说，常见的补救方法似乎是在所有has_many和has_one关联上使用"dependent::destroy".我正在这样做，但仍然出现错误.

I see a lot of posts here with similar issues, but none has worked. Specifically, the common remedy seems to be to use "dependent: :destroy" on all has_many and has_one associations. I am doing this but am still getting the error.

我做错了什么?

下面是我的代码:

class User < ApplicationRecord
  devise :database_authenticatable, :registerable,
  :recoverable, :rememberable, :trackable, :validatable

  has_one :profile, dependent: :destroy
  has_many :bikes, dependent: :destroy
  has_many :bookings, dependent: :destroy
  has_many :rented_bikes, through: :bookings, source: :bike
  has_many :conversations, dependent: :destroy
  has_many :likes, dependent: :destroy
  has_many :liked_bikes, through: :likes, :source => :bike
  has_many :viewed_bikes, through: :views, :source => :bike
  has_many :views, dependent: :destroy
  has_many :reviews, dependent: :destroy
end

class Profile < ApplicationRecord
  belongs_to :user
end

class Bike < ApplicationRecord
  belongs_to :user
  has_many :images, dependent: :destroy
  has_many :bookings, dependent: :destroy
  has_many :booked_users, through: :bookings, source: :user
  has_many :conversations, dependent: :destroy
  has_many :likes, dependent: :destroy
  has_many :liking_users, :through => :likes, :source => :user
  has_one :amenity, dependent: :destroy
  has_many :places, dependent: :destroy
  has_many :views, dependent: :destroy
end

class Booking < ApplicationRecord
  belongs_to :bike
  belongs_to :user

  has_one :review, dependent: :destroy

  validates :date_start, presence: true
  validates :date_end, presence: true
  validates :user_id, presence: true
end

class Conversation < ApplicationRecord
  belongs_to :user
  belongs_to :bike

  has_many :messages, dependent: :destroy
end

class Like < ApplicationRecord
  belongs_to :user
  belongs_to :flat
end

class View < ApplicationRecord
  belongs_to :user
  belongs_to :flat
end

class Review < ApplicationRecord
  belongs_to :user
  belongs_to :booking
end

迁移:

class ChangeCustomerIdToUserId < ActiveRecord::Migration[5.1]
  def change
    rename_column :users, :customer_id, :client_id
  end
end

推荐答案

您一次遇到了几个问题:

You have a couple problems happening at once:

1. SQLite不支持重命名列，因此ActiveRecord驱动程序以困难的方式实现列重命名:使用新列名创建新表，复制所有数据，删除原始表，重命名新表. 请注意，此操作最近已更改，因此最新的SQLite确实支持就地重命名列.
2. 您在其他表中引用了users表的外键.

1. SQLite doesn't support renaming columns so the ActiveRecord driver implements column renaming the hard way: create a new table with the new column names, copy all the data, drop the original table, rename the new one. Note that this has recently changed so the latest SQLite does support renaming columns in-place.
2. You have foreign keys in other tables that reference your users table.

(2)是在迁移过程中触发错误的原因:当有外键引用时，您不能删除表(请参见(1))因为删除表会违反那些外键.

(2) is what is triggering your error during your migration: you can't drop a table (see (1)) when there are foreign keys referencing it since dropping the table would violate those foreign keys.

解决方案是删除迁移中所有有问题的FK，然后执行rename_column，然后再次添加所有FK.或者更好的是，停止使用SQLite来支持具有对列重命名的适当支持的更好的数据库(例如PostgreSQL或MySQL或要在其上部署应用程序的任何数据库).另一种选择是尝试在迁移过程中关闭FK并将其重新打开，例如:

The solution is to drop all the offending FKs in your migration, then do the rename_column, and then add all the FKs back again. Or better, stop using SQLite in favor of a better database (such as PostgreSQL or MySQL or whatever you're deploying the app on) that has proper support for column renaming. Another option would be to try to turn off FKs and turn them back on in your migration, something like:

connection.execute("PRAGMA defer_foreign_keys = ON")
connection.execute("PRAGMA foreign_keys = OFF")
rename_column :users, :customer_id, :client_id
connection.execute("PRAGMA foreign_keys = ON")
connection.execute("PRAGMA defer_foreign_keys = OFF")

可能会工作.

三个月前，有一个对Rails的提交应该可以解决此问题.但我认为它尚未成为任何发行版.

There was a commit made to Rails three months ago that should fix this problem but I don't think it has made it into any release version yet.

这篇关于当重命名Users表中的列时，获取:SQLite3 :: ConstraintException:FOREIGN KEY约束失败:DROP TABLE"users"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！