John Gonzales wrote:
i am creating my own little blog and i am trying to create a comments table
that uses the primary key of my blog table as a foreign key. i've tried the
only two ways that i know how and both ways yielded the same error:
#1005 - Can't create table '.\mydatabase\comments.frm' (errno: 150)
You can look up what error numbers mean with perror:
~: perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
i orginally created the comments table before i read about FOREIGN KEYS, so
i tried executing the following command:
ALTER TABLE comments
ADD FOREIGN KEY(comment_journal_id)
REFERENCES journal(journal_id)
ON DELETE CASCADE ON UPDATE CASCADE;
which resulted with the same error as listed above. so not really knowing
what i was doing, i decided to try and create the comments table from
scratch using the following the command thinking that it might work (which
it didn't):
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
now before i go and do something stupid, i thought i might ask for some help
on this issue :D both my blog and comments tables are InnoDB.
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]
