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?

"This error generally occurs because we have some values in the referencing field of the child table, which do not exist in the referenced/candidate field of the parent table." Possible duplicate of [ERROR 1452: Cannot add or update a child row: a foreign key constraint fails](https://stackoverflow.com/questions/21659691/error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails) — Raymond Nijland, Dec 13 '18 at 16:30

score 1 · Accepted Answer · answered Dec 14 '18 at 03:35

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.

Add foreign key in Rails

1 Answers1