"Nanu Kalmanovitz" <[EMAIL PROTECTED]> wrote on 03/10/2006 02:35:08 PM:
> Hi! > > Server system SBS (Novell Small Business suite) 6.5 sp 1 with MySQL > ver. 4.0.15a, PHP 4.2.3, all of them on same machine. > > I just finished create a new DB called TIULIM (InnoDB) with 3 tables > (Sites, Tracks & Pathes). > > Now, using MySQL Query Browser ver. 1.1.15, I'm trying to build FOREIGN > KEYS. > > The above tool is generating the following query: > > ALTER TABLE `tiulim`.`pathes` ADD CONSTRAINT `FK_pathes_1` FOREIGN KEY > `FK_pathes_1` (`Site_ID`) > REFERENCES `sites` (`Site_ID`) > ON DELETE RESTRICT > ON UPDATE RESTRICT, > ADD CONSTRAINT `FK_pathes_2` FOREIGN KEY `FK_pathes_2` (`Track_ID`) > REFERENCES `tracks` (`Track_ID`) > ON DELETE RESTRICT > ON UPDATE RESTRICT; > > After executing the query, it display the error message: > > MySQL Error Number 1005 > Can't create table './tiulim/#sql-84_169.frm' (errno: 150) > > What is the problem? > How to solve it? > > TIA > > Nanu > > Another FAQ.... A) Whole databases are not handled by InnoDB. The individual tables are. Check the results of "SHOW CREATE TABLE table_name_here" for each table you are linking from and linking to. You should see ENGINE = InnoDB after the last ) but before the final ; in each definition. If not, you need to change your tables to use that engine. B) Each column participating in either end of a FOREIGN KEY must be of the same data type and size and signedness (everything about the columns must match except the names and comments) C) Each column participating in either end of a FOREIGN KEY must be the leftmost column of an index. Simply declaring a FOREIGN KEY constraint will not automatically create an index on the child table if one is missing. D) When all else fails, read the detailed error message returned by InnoDB by using the SHOW INNODB STATUS; command More details here: http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
