15 messages in com.mysql.lists.mysqlRe: need help with foreign keys, new ...
FromSent OnAttachments
John Gonzales19 Aug 2005 19:08 
Kishore Jalleda19 Aug 2005 20:34 
John Gonzales20 Aug 2005 00:10 
Jasper Bryant-Greene20 Aug 2005 00:17 
John Gonzales20 Aug 2005 00:46 
Thurgood Alex20 Aug 2005 01:21 
John Gonzales20 Aug 2005 09:28 
Michael Stassen20 Aug 2005 21:06 
Michael Stassen20 Aug 2005 21:10 
John Gonzales24 Aug 2005 04:10 
Pat Adams24 Aug 2005 06:17 
Roger Baklund24 Aug 2005 07:16 
John Gonzales24 Aug 2005 09:00 
Enrique Sanchez Vela18 Oct 2005 03:11 
Enrique Sanchez Vela18 Oct 2005 03:12 
Subject:Re: need help with foreign keys, new to mysql
From:Roger Baklund (rog@charlott.no)
Date:08/24/2005 07:16:09 AM
List:com.mysql.lists.mysql

Pat Adams wrote:

On Wed, 2005-08-24 at 06:11 -0500, John Gonzales wrote:

CREATE TABLE `journal` ( `journal_id` int(10) unsigned NOT NULL auto_increment, [snip] CREATE TABLE comments ( comment_id INT, comment_journal_id INT, INDEX jrn_id (journal_id),

Here you are defining an index named jrn_id on a column called journal_id. This column must be defined in this table. I suppose it is a mistake, it should be:

INDEX jrn_id (comment_journal_id),

FOREIGN KEY (comment_journal_id) REFERENCES journal (journal_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB;

I got this as a respsone: Key column 'journal_id' doesn't exist in table

See above.

From the manual: http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html

"Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and the signedness of integer types has to be the same."

You've got an INT in comments table and an int(10) unsigned in the journal table. They need to match in signedness.

I don't dispute this, but it was not the reason for the error message. :)

-- Roger