re:FOREIGN KEY{ERROR:1005/150}

--------------060607060706060407070107
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hello,
I have been browsing on mysql manual

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


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


--------------060607060706060407070107--

Re: re:FOREIGN KEY{ERROR:1005/150}

On Wed, Feb 11, 2009 at 7:36 PM, mrfroasty wrote:
> Hello,
> I have been browsing on mysql manual
>
> 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
>

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: re:FOREIGN KEY{ERROR:1005/150}

--------------080500090405040101010300
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Thanks Chris,

Hotel table is indeed innodb, and items_no is int(16)....here is hotel table
CREATE TABLE hotel (
items_no int(16) NOT NULL auto_increment,
INDEX (items_no),
PRIMARY KEY (items_no)
)ENGINE=INNODB;

I did read the comment with the same error 150, but I cant grasp pretty
well whats wrong with this table.

P:S
Keep in mind that I am not very well experienced in database design.

Regards,

chris smith wrote:
> On Wed, Feb 11, 2009 at 7:36 PM, mrfroasty wrote:
>
>> Hello,
>> I have been browsing on mysql manual
>>
>> 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
>>
>>
>
> 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) ?
>
>


--
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


--------------080500090405040101010300--

Re: 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