Add foreign key in Rails

Question

I have two tables, casino_users and accounts.

I want to make casino_users.username a foreign key that references accounts.email.

I tried this:

class AddForeignKeyToCASinoUsers < ActiveRecord::Migration[5.2]
  def up
    add_foreign_key :casino_users, :accounts, column: :username, primary_key: "email"
  end 

  def down
    remove_foreign_key :casino_users, column: :username
  end 
end

but it gives me an error Cannot add or update a child row: a foreign key constraint fails.

Is there something wrong with the syntax I am using?

Answer 1

First guess is that you have data in the existing tables which is incompatible with the constraint you are now adding. If that doesn't seem to be the issue would be good to give a little more context on when this error occurred. When you tried to run the migration is what I am assuming.

PS You may get into trouble by attempting to use a set a primary key which is syntatically meaningful and also subject to change. If you leave the primary key set to id, and linke users to accounts by id, you will have an easier time of it when, for example some day you ant to allow users to change their email address. If you want to make sure there is one account associated with an email address that is easy enough to achieve with a uniq constraint on the email field.