Re: foreign keys.

[Chris Nolan]

Hi,

Have you considered just normalising the schema a bit more? It looks 
like you're attempting to duplicate data within the
table (which you don't strictly need) and duplicating Fname and Lname 
between Blah and foo. Why not just have an
AUTO_INCREMENT column (or some other unique row identifier) in Blah and 
reference that? It will help performance
and reduce your database size.

Regards,

Chris

Aftab Jahan Subedar wrote:

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.





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