On Mon, 15 Dec 2003 09:22 pm, Victoria Reznichenko wrote:
> Mofeed Shahin <[EMAIL PROTECTED]> wrote:
> > On Mon, 15 Dec 2003 12:42 pm, Paul DuBois wrote:
> >> At 11:09 +1030 12/15/03, Mofeed Shahin wrote:
> >> >I'm trying to create a bunch of tables in MySQL. I'm having problems
> >> > creating the following table :
> >> >
> >> >CREATE TABLE foo(
> >> > ID INT PRIMARY KEY,
> >> > note VARCHAR(50),
> >> > FOO_ID INT,
> >> > FOREIGN KEY (FOO_ID) REFERENCES foo(ID)
> >> >) TYPE=INNODB;
> >> >
> >> >The error I get is the following :
> >> >ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150)
> >> >
> >> >I found out that errno 150 means that it didn't like the Foreign key
> >> >constraint.
> >> >
> >> >Does MySQL not support this type of Foreign Key constraint ?
> >>
> >> If does. However, a foreign key must be indexed, and you have declared
> >> no index on FOO_ID. Try this:
> >>
> >> CREATE TABLE foo(
> >> ID INT PRIMARY KEY,
> >> note VARCHAR(50),
> >> FOO_ID INT,
> >> INDEX (FOO_ID),
> >> FOREIGN KEY (FOO_ID) REFERENCES foo(ID)
> >> ) TYPE=INNODB;
> >
> > Thanks, but I just did, and I got the same error message.
>
> Paul's example works fine for me. What version of MySQL do you use?
yeah, sorry Paul's example works here as well. But the actual create statement
I'm using here is failing. My create statement is stightly different, and I
didn't think it would make a difference (ooops!!). So here is the actual
create statement that is failing ;
CREATE TABLE Blah (
ID INT PRIMARY KEY,
Fname VARCHAR (50),
Lname VARCHAR (50),
UNIQUE (Fname, Lname)
) TYPE=INNODB;
CREATE TABLE foo (
ID INT PRIMARY KEY,
note VARCHAR(50),
Fname VARCHAR(50),
Lname VARCHAR(50),
FOO_ID INT,
INDEX(FOO_ID),
FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname)
) TYPE=INNODB;
Once again sorry for the confusion. It must be a problem with adding the
second foreign key.....
Mof.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
