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>.
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?
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?
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
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
