Bug #32562 Unable to craete foreign key with different field names
Submitted: 21 Nov 2007 8:32 Modified: 21 Nov 2007 10:21
Reporter: Grzegorz Łyp Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.0.45-community-nt OS:Microsoft Windows (XP SP2 (all updates))
Assigned to: CPU Architecture:Any
Tags: foreign key;contraint;create table

[21 Nov 2007 8:32] Grzegorz Łyp
Description:
I have a table:

CREATE TABLE  `lyp_mag`.`rodzaje_dow` (
  `Rodzaj` varchar(20) NOT NULL,
  `Opis` varchar(50) default NULL,
  `PR` smallint(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp852;

and I want to create a table referencing the above table:

CREATE TABLE `lyp_mag`.`dow` (
  `RD` VARCHAR(20) NOT NULL,
  `ND` INT UNSIGNED NOT NULL,
  `SymKli` VARCHAR(20),
  `Data` DATETIME NOT NULL,
  `PR` SMALLINT NOT NULL,
  PRIMARY KEY (`RD`, `ND`),
  CONSTRAINT FOREIGN KEY (`SymKli`)
    REFERENCES `kli` (`SymKli`),
  CONSTRAINT FOREIGN KEY (`RD`)
    REFERENCES `rodzaje_dow` (`Rodzaj`)
);

seems fine but I get the following error:
Can't create table '.\lyp_mag\dow.frm' (errno: 150)

I found out that if the names of the fields in the source and destination tables being a part of FOREIGN KEY are different, my database cannot create such constraint.

Here is my version on MySQL:
 Variable_name            Value                         
 -----------------------  ----------------------------- 
 protocol_version         10                            
 version                  5.0.45-community-nt           
 version_comment          MySQL Community Edition (GPL) 
 version_compile_machine  ia32                          
 version_compile_os       Win32 

Please give m the hint what goes wrong.

How to repeat:
Try to create tables like in the description. My database have cp852 character set with cp_852_general_ci collation.

Suggested fix:
MySQL should be able to create foreign key constraints that support different field names on the source and destination tables.
[21 Nov 2007 10:21] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You have error in the statement.

Please read about restrictions which both columns should satisfy at http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html