0

I have tables and some of the columns should be foreign key; how can I force this relationship?

ALTER TABLE tbl1 ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) references tbl2(user_id);

But it gives me the following error:

error code:1215: cannot add foreign key constraint!

What is the solution in this case?

Reigel Gallarde
  • 64,198
  • 21
  • 121
  • 139
  • 1
    They need to be exactly the same data type in both tables. They are? – Lucas Henrique May 13 '14 at 01:04
  • Thanks; it said duplicate key; because I had the same foreign key name in another table; so I changed the name and now it works; but does the name matter here? What is mattered I guess just to force the relationship? Is this foreign key name used anywhere else? my real column name is what it used to be! so is the foreign key name a big deal? –  May 13 '14 at 01:36

2 Answers2

1

Reasons that you may get foreign key constraing error:

  1. You are not using InnoDB as the engine on all tables
  2. You are trying to reference a nonexistent key on the target table. Make sure that it is a key on the other table (it can be a primary or unique key)
  3. The types of the columns are not the same (exception is the column on the referecing table can be nullable).

Check these.

Also, you should set foreign_key_checks=0 before running the DDL so you can create the tables in an arbitrary order rather than needing to create all parent tables before the relevant child tables.

Font: MySQL Error 1215: Cannot add foreign key constraint

Community
  • 1
  • 1
Lucas Henrique
  • 1,380
  • 1
  • 11
  • 15
  • @user3421904 Solved the problem? – Lucas Henrique May 13 '14 at 01:28
  • Thanks; it said duplicate key; because I had the same foreign key name in another table; so I changed the name and now it works; but does the name matter here? What is mattered I guess just to force the relationship? Is this foreign key name used anywhere else? –  May 13 '14 at 01:34
  • my real column name is what it used to be! so is the foreign key name a big deal? –  May 13 '14 at 01:42
  • 1
    @user3421904 The name will not be used for anything, so no matter. Held the record, then it will force the relationship. I have not used in any case the name of the foreign key. Both that there is another way to create the table informing foreign keys without creating names for them. – Lucas Henrique May 13 '14 at 01:45
0

If any of the existing values of the user_id does not match with a value on the foreign table or are null, you will not be able to enforce a non-nullable foreign key constraint. You should first update the column values to the correct foreign data referenced before enforcing the constraint.

stripthesoul
  • 362
  • 1
  • 8
  • Thanks; it said duplicate key; because I had the same foreign key name in another table; so I changed the name and now it works; but does the name matter here? What is mattered I guess just to force the relationship? Is this foreign key name used anywhere else? –  May 13 '14 at 01:35
  • my real column name is what it used to be! so is the foreign key name a big deal? –  May 13 '14 at 01:41
  • Yea, you cannot have the same foreign key name in the same database twice – stripthesoul May 13 '14 at 01:52