0

I use MySQL with InnoDB engine. I double-checked type of columns. But always have:

Error Code: 1215. Cannot add foreign key constraint

I tried:

ALTER TABLE `mail`.`boxes`  
    ADD CONSTRAINT FK_id 
    FOREIGN KEY (id) 
    REFERENCES `mail`.`users` (id)
    ON UPDATE NO ACTION 
    ON DELETE NO ACTION;

and

ALTER TABLE `mail`.`boxes` 
  ADD FOREIGN KEY (id)
  REFERENCES `mail`.`users` (id)

Nothing works(((

Please, help, what I am doing wrong (except choosing MySQL :-) )?

cathulhu
  • 641
  • 1
  • 9
  • 20
serg
  • 1,003
  • 3
  • 16
  • 26

2 Answers2

0

To check what exactly the problem is, use:

SHOW ENGINE INNODB STATUS\G

There is section "last foreign key error". Look at: http://dev.mysql.com/doc/refman/5.0/en/innodb-monitors.html

My guess is that data type od mail.boxes (id) and mail.users (id) is not the same. (E.g. smallint in one table and integer in second one).

Data in table on which you're trying to create FK could possibly also be problem (are your mailbox ids the same as id of existing users?)

cathulhu
  • 641
  • 1
  • 9
  • 20
  • I deleted all data from tables and checked again for datat types, and they are the same( – serg Jul 12 '13 at 11:34
  • INNODB STATUS writes something like: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for correct foreign key definition. – serg Jul 12 '13 at 11:37
  • Do you have any index on mail.users (id) column? E.g. creating Primary Key on that column should help. – cathulhu Jul 12 '13 at 11:52
  • yep mail.users (id) is already primary key, and still not works( – serg Jul 12 '13 at 14:31
  • Can you provide DDL's ('CREATE TABLE' statements) for both tables? Maybe, I don't know, mail.boxes.id is AUTOINCREMENT? Is 'id' also primary key in mail.boxes table? Try to create another column (called e.g. user_id) and create FK on it: ALTER TABLE mail.boxes ADD FOREIGN KEY (user_id) REFERENCES mail.users (id). – cathulhu Jul 12 '13 at 14:44
0

If table contains data then you are not able to add foreign key you drop table object and recreate use below reference for the same

Basics of Foreign Keys in MySQL?

Community
  • 1
  • 1
VasantG
  • 16
  • 1