[SOLVED] Newbie Help - Foreign Key Not 'Taking'

[powerpants]

Hi everyone,

 

I'm setting up my first MySQL database, but none of my foreign keys seem to be getting configured correctly according to my SHOW CREATE TABLE output.

 

For example, I have a table called image_person which has two attributes that should be foreign keyed to two different tables which hold the image_id's and person_id's.  I set up the table using the following command:

 

CREATE TABLE image_person(
image_id int NOT NULL,
person_id int NOT NULL,
FOREIGN KEY (image_id) REFERENCES image(image_id),
FOREIGN KEY (person_id) REFERENCES person(person_id)
);

 

But, when I SHOW CREATE TABLE image_person, I get......

 

| image_person | CREATE TABLE `image_person` (
  `image_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  KEY `image_id` (`image_id`),
  KEY `person_id` (`person_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 

 

Shouldn't I see the foreign key information?  What am I doing wrong?

 

I'm running MySQL 5.0.45

 

Thanks!

[fenway]

No FKs with MyISAM engine... you need InnoDB.

[powerpants]

Ok, thanks.  I looked into InnoDB, and here's my new query:

CREATE TABLE image_person(
image_id int NOT NULL,
person_id int NOT NULL,
FOREIGN KEY (image_id) REFERENCES image(image_id),
FOREIGN KEY (person_id) REFERENCES person(person_id)
) ENGINE=INNODB;

 

And, here's the error I get:

ERROR 1005 (HY000): Can't create table './davidland/image_person.frm' (errno: 150)

 

From what I've been reading, InnoDB and not-InnoDB tables are freely mixable in databases, right?  Or could this problem be that I'm trying to foreign key to a table which is not InnoDB?

 

Thanks for your reply!

 

 

 

[fenway]

You may want to have a look here... they can't be mixed "freely".

[powerpants]

That's just what I needed.  Thanks!