| 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: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 5.0.45-community-nt | OS: | Windows (XP SP2 (all updates)) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | foreign key;contraint;create table | ||
[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
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.