[PHP-DB] re:FOREIGN KEY{ERROR:1005/150}
Hello, I have been browsing on mysql manual http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html with not much of success.I am trying to put 2 foreign keys(from different external table) on my table, but if I put the 2nd one it fails.here is my table CREATE TABLE pack_accomodation ( id int(16) NOT NULL auto_increment, pack_id int(16) NOT NULL , hotel_id int(16) NOT NULL , PRIMARY KEY (id), INDEX (pack_id), FOREIGN KEY (pack_id) REFERENCES pack(items_no) ON DELETE CASCADE ON UPDATE CASCADE, INDEX (hotel_id), FOREIGN KEY (hotel_id) REFERENCES hotel(items_no) )ENGINE=INNODB; I can get rid of the 2nd foreign key with programming, but I think it should be possible to have them both...I just I cant figure out why it fails with this error: ERROR 1005 (HY000): Can't create table './tabasam/pack_accomodation.frm' (errno: 150) For description of the error http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html Thanks guys, any help would really appreciate it. -- Extra details: OSS:Gentoo Linux-2.6.25-r8 profile:x86 Hardware:msi geforce 8600GT asus p5k-se location:/home/muhsin language(s):C/C++,VB,VHDL,bash Typo:40WPM url:http://mambo-tech.net
Re: [PHP-DB] re:FOREIGN KEY{ERROR:1005/150}
On Wed, Feb 11, 2009 at 7:36 PM, mrfroasty mrfroa...@gmail.com wrote: Hello, I have been browsing on mysql manual http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html with not much of success.I am trying to put 2 foreign keys(from different external table) on my table, but if I put the 2nd one it fails.here is my table CREATE TABLE pack_accomodation ( id int(16) NOT NULL auto_increment, pack_id int(16) NOT NULL , hotel_id int(16) NOT NULL , PRIMARY KEY (id), INDEX (pack_id), FOREIGN KEY (pack_id) REFERENCES pack(items_no) ON DELETE CASCADE ON UPDATE CASCADE, INDEX (hotel_id), FOREIGN KEY (hotel_id) REFERENCES hotel(items_no) )ENGINE=INNODB; I can get rid of the 2nd foreign key with programming, but I think it should be possible to have them both...I just I cant figure out why it fails with this error: ERROR 1005 (HY000): Can't create table './tabasam/pack_accomodation.frm' (errno: 150) For description of the error http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html Did you read the comments on that page? The top 2 give clues about what else to check. Is the 'hotel' table innodb as well? What type is items_no in the hotel table? Is it int(16) ? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] re:FOREIGN KEY{ERROR:1005/150}
This worked for me: CREATE TABLE hotel ( items_no int(16) NOT NULL auto_increment, INDEX (items_no), PRIMARY KEY (items_no) )ENGINE=INNODB; CREATE TABLE pack ( items_no int(16) NOT NULL auto_increment, INDEX (items_no), PRIMARY KEY (items_no) )ENGINE=INNODB; CREATE TABLE pack_accomodation ( id int(16) NOT NULL auto_increment, pack_id int(16) NOT NULL , hotel_id int(16) NOT NULL , PRIMARY KEY (id), INDEX (pack_id), FOREIGN KEY (pack_id) REFERENCES pack(items_no) ON DELETE CASCADE ON UPDATE CASCADE, INDEX (hotel_id), FOREIGN KEY (hotel_id) REFERENCES hotel(items_no) )ENGINE=INNODB; But my tables are all empty. Maybe you have duplicate id's in one of the tables? No idea what else to suggest apart from joining the mysql list (http://lists.mysql.com/) to see if they have a suggestion. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php