FOREIGN KEYS

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

Thanks!

I found that the problem was with the PRIMARY keys\indexes definitions.
 
I changed all the PRIMARY KEYS from MEDIUMINT to INTEGER and it solved the
problem.
 
What other types of columns can be used with INNODB as PRIMARY indexes for
FOREIGN KEYS?
 
TIA 
 
Nanu


>>> Nanu Kalmanovitz<nanu@kalmanovitz.co.il> 10/03/2006 20:03:21
>>>
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