Sorry for not replying as quickly as I usually do, I ran into some other server issues. Please take a look at my comments to your post.
-----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: August 20, 2005 11:11 PM Cc: John Gonzales; mysql@lists.mysql.com Subject: Re: need help with foreign keys, new to mysql Sorry to reply to my own message, but I meant to add that you can read about defining foreign key constrints in the manual <http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html>. [jg.] thanks, this is what I originally based my command off of. Michael Michael Stassen wrote: > There are a few possibilities here: > > 1. Both tables must be InnoDb. I can see table comments is InnoDb. Is > table journal InnoDb? > [jg.] yes both tables are innodb > 2. Both ends of the foreign key must be the leftmost column in an > index. I can see an index on comments.journal_id. Does column > journal_id come first in an index in table journal? > [jg.] I don't quite understand what you mean by "leftmost column in an index", but yes journal_id is the first field in table journal, but that's probably not what you meant. Take a look at the result from SHOW CREATE TABLE: CREATE TABLE `journal` ( `journal_id` int(10) unsigned NOT NULL auto_increment, `journal_category` int(10) unsigned NOT NULL default '1', `journal_datetime_created` timestamp NOT NULL default CURRENT_TIMESTAMP, `journal_datetime_modified` timestamp NOT NULL default '0000-00-00 00:00:00', `journal_title` varchar(50) NOT NULL default 'no title', `journal_entry` blob NOT NULL, PRIMARY KEY (`journal_category`), KEY `journal_category` (`journal_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > 3. You are misplacing your parentheses. MySQL looks at parenthesis > placement to help distinguish between functions and non-function > identifiers. In general, when there is no space between a word and a > left parenthesis, '(', the word is a function name, but when there is a > space in between, the word is not a function. Hence, "journal( > journal_id )" would be journal_id as input to the function journal(), > where "journal (journal_id)" is what you want. Now, the parser may be > smart enough to notice there is no function journal(), so this may not > be a problem here, but it still something to be aware of, lest it bite > you later. > > You could check on the first two with a simple > > SHOW CREATE TABLE journal; > > You could fix #3 by changing the CREATE TABLE statement for comments: > > CREATE TABLE comments > ( > comment_id INT, > journal_id INT, > INDEX jrn_ind (journal_id), > FOREIGN KEY (journal_id) REFERENCES journal (journal_id) > ON DELETE CASCADE ON UPDATE CASCADE > ) TYPE = INNODB > [jg.] CREATE TABLE comments ( comment_id INT, comment_journal_id INT, INDEX jrn_id (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 > Michael > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
