Re: foreign keys.

Mon, 15 Dec 2003 18:15:59 -0800 

If you have foreign key then add a key for each,
so the
> CREATE TABLE foo (
>    ID INT PRIMARY KEY,
>    note VARCHAR(50),
>    Fname VARCHAR(50),
>    Lname VARCHAR(50),
>    FOO_ID INT,
>    INDEX(FOO_ID),
      KEY(Fname,Lname), #here this one--if it does not work,its not me
>    FOREIGN KEY (FOO_ID) REFERENCES foo(ID),
>    FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname)
> ) TYPE=INNODB;



Mofeed Shahin wrote:
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.



--

Aftab Jahan Subedar
CEO/Software Engineer
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
http://www.DhakaStockExchangeGame.com/ - [EMAIL PROTECTED]
http://www.CEOBangladesh.com/ - [EMAIL PROTECTED]
http://www.geocities.com/jahan.geo/ - [EMAIL PROTECTED]
sms://+447765341890
tel://+88027519050
EMail://[EMAIL PROTECTED] - Directly to my notebook



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to