Its not my data. I'm simply writting a program that reads in the meta data, and does stuff with it client side. I just need to be able to handle all sorts of stuff, and I wanted to test it on MySQL (amongst others).
Mof. On Tue, 16 Dec 2003 11:49 am, Chris Nolan wrote: > 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]