UPDATE:
Well, it seems to me that this topic deserves some more attention, so I'm going to investigate it thoroughly.
From this page I found that:
a table [...] must have indexes on the referenced keys. If these are not satisfied [read: "if they are not present for those keys"], MySQL returns error number 1005 and refers to error 150 in the error message.
More in detail, in the rest of the update I'm assuming the following command (as it is reported on the same page I linked above) as reference:
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option]
@Dhruva Sagar: I think you were talking about this one:
If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.
@payal: Check this (same page as above...):
InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.
Furthermore I suggest using the following command to check when errors occur: SHOW ENGINE INNODB STATUS, which displays a detailed explanation of the most recent foreign key error in the server.
I'm trying to replicate this error today; I'll drop you the results as soon as I can.
----------------- old updates -----------------
27-08-2007 - 10:08 (OLD UPDATE!!!)
UPDATE:
It seems the problem is caused by the "ON UPDATE" and "ON DELETE" clauses: they have to be set to the same policy, i.e. you have to write "ADD CONSTRAINT 'new_fk_constraint' FOREIGN KEY 'new_fk_constraint' ('fieldA1') REFERENCES 'tableB' ('fieldB2') ON DELETE RESTRICT ON UPDATE RESTRICT" or "ON DELETE UPDATE ON UPDATE UPDATE", while "ON DELETE SET NULL ON UPDATE RESTRICT" won't work.
Indexes are not the cause of the problem: when you create a foreign key an index for the field involved on the foreign key is automatically created by the MySQL environment, so you don't need to manually create it before adding the foreign key (read: "Bull-Shit at 09:41, Good-Shit at 10:08").
27-08-2007 - 09:41 (OLD UPDATE!!!)
If you want to place a foreign key from field 1 on table A to field 2 on table B, first of all you need to create an index for the field 1 on table A.
If you don't create the index before attempting to create the foreign key you are likely to be stopped by the error "MySQL Error Nr. 1005- Can't create table...".
I experienced this situation while using InnoDB.
The error my friend is not due to a syntax error but in my opinion it is because of a duplicate foreign key constraint name. The same name must have been used in some other table already.I am not sure if this is a bug or if this is how the implementation should be but that's the reason for the error I believe.
ReplyDeletedhruva sagar, you right, I have the same problem and I change the name of foreing key of the table and it works.
ReplyDeletehi
ReplyDeletecan we make a foreign key for table A which refer a primary key of table A
Hi Payal, are you asking me something different from the post's argument or proposing a possible solution for the problem I discussed?
ReplyDeleteCheck whether there are any records in the tables. Should be removed before creating foreign key constraint
ReplyDeleteHi guys. I'm looking into this problem again and I'll probably post some updates soon (I think a few hours would suffice to figure out something new). See you soon.
ReplyDeleteHie guys,Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same.Also check for the same name of foreign keys used
ReplyDeleteHey Guys make sure while referencing any field from foreign table , both field data types, attributes should match for eg. unsigned, autofillZero etc....
ReplyDeleteThere are more than 10 reasons why you can get errors like this. There is an exhaustive list here:
ReplyDeleteMysqL Foreign Key Errors and Errno 150
Thank you very much David.
ReplyDelete