Hi!
You have not created an index on your foreign key. From
http://www.innodb.com/ibman.html :
...........
An example:
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id))
TYPE=INNODB;
Both tables have to be InnoDB type and there must be an index where the
foreign key and the referenced key are listed as the first columns. InnoDB
does not auto-create indexes on foreign keys or referenced keys: you have to
create them explicitly.
If MySQL gives the error number 1005 from a CREATE TABLE statement, and the
error message string refers to errno 150, then the table creation failed
because a foreign key constraint was not correctly formed.
.............
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB
Demirchyan Oganes-AOD098 wrote in message ...
>
>I have the following create table, and I get the following error when I
create it.
>
>It used to work before. Can anyone help?
>
>CREATE TABLE PROTOCOL_GROUPING(
>PROTOCOL_GROUPING_ID MEDIUMINT(20),
>GROUP_NAME VARCHAR(50) NOT NULL,
>PROTOCOL_ID MEDIUMINT(20) NOT NULL,
>PROTOCOL_STEP_NO MEDIUMINT(10) NOT NULL,
> foreign key (PROTOCOL_ID, PROTOCOL_STEP_NO) REFERENCES
PROTOCOL_STEP(PROTOCOL_ID, PROTOCOL_STEP_NO), UNIQUE INDEX
PROTOCOL_GROUPING_PK (PROTOCOL_GROUPING_ID))Type=InnoDB;
>
>I get the following error: can't create table .\test\protocol_grouping.frm
(errno 150)
>
>Oganes Demirchyan
>
>Oganes Demirchyan
>Motorola Life Science
>757 S.Raymond
>Pasadena, CA 91105
>Tel: 626-584-5900
>email: [EMAIL PROTECTED]
>
>-----Original Message-----
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
>Sent: Monday, February 04, 2002 11:02 AM
>To: Demirchyan Oganes-AOD098
>Subject: Re: mysql create table error
>
>Your message cannot be posted because it appears to be either spam or
>simply off topic to our filter. To bypass the filter you must include
>one of the following words in your message:
>
>sql,query
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
