SQL

[clio_chris]

Its a longshot, but is anyone here any good with MySQL? Ive got a project to do and have done most of it but one 'create table' is giving me some grief and ive got no idea how to fix it. Keeps coming up with:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( Del_List_ID ),
Constraint FK_Order_Line FOREIGN KEY (Del_List_ID) REFERENCES ' at line 12.

The create table looks like this:

CREATE TABLE Order_Line
(Line_ID INT(4) NOT NULL,
StOrd_ID INT(4) NOT NULL,
Del_List_ID INT(4) NOT NULL,
Filling_ID VARCHAR(30) NOT NULL,
Bread_ID VARCHAR(30) NOT NULL,
Quantity INT(3) NOT NULL,

Constraint Pk_Order_Line Primary Key(Line_ID),
INDEX ( StOrd_ID ),
Constraint FK_Order_Line FOREIGN KEY (StOrd_ID) REFERENCES Standing_Order( StOrd_ID ) ON DELETE CASCADE)
INDEX ( Del_List_ID ),
Constraint FK_Order_Line FOREIGN KEY (Del_List_ID) REFERENCES Delivery_List( List_ID ) ON DELETE CASCADE)
INDEX ( Filling_ID ),
Constraint FK_Order_Line FOREIGN KEY (Filling_ID) REFERENCES Filling( Filling_ID ) ON DELETE CASCADE)
INDEX ( Bread_ID ),
Constraint FK_Order_Line FOREIGN KEY (Bread_ID) REFERENCES Bread( Bread_ID ) ON DELETE CASCADE)
TYPE = INNODB;

Any help MUCH appreciated!
Chris.

 

[Alex H]

ON DELETE CASCADE)

What's the ) bracket for?

edit: surely they should be commas?

 

[iain187]

also what is the bracket at the start of the 2nd line for

 

[clio_chris]

Ill be honest, im not exactly sure lol, its just the way ive been shown.

However ive removed the brackets you guys have mentioned and now I get this message...

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Line_ID INT(4) NOT NULL,
StOrd_ID INT(4) NOT NULL,
Del_List_ID INT(4) NOT NULL' at line 2

 

[Alex H]

also what is the bracket at the start of the 2nd line for

that bracket needs to be there, and there should be an accompanying one just before the ; at the end.

 

[clio_chris]

Right, ive removed the brackets from the CASCADE bits, and left the other one....


#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= INNODB' at line 18

 

[Alex H]

Post the whole 'create table' sql in now.

 

[clio_chris]

CREATE TABLE Order_Line
(Line_ID INT( 4 ) NOT NULL ,
StOrd_ID INT( 4 ) NOT NULL ,
Del_List_ID INT( 4 ) NOT NULL ,
Filling_ID VARCHAR( 30 ) NOT NULL ,
Bread_ID VARCHAR( 30 ) NOT NULL ,
Quantity INT( 3 ) NOT NULL ,
CONSTRAINT Pk_Order_Line PRIMARY KEY ( Line_ID ) ,
INDEX ( StOrd_ID ) ,
CONSTRAINT FK_Order_Line FOREIGN KEY ( StOrd_ID ) REFERENCES Standing_Order( StOrd_ID ) ON DELETE CASCADE ,
INDEX ( Del_List_ID ) ,
CONSTRAINT FK_Order_Line FOREIGN KEY ( Del_List_ID ) REFERENCES Delivery_List( List_ID ) ON DELETE CASCADE ,
INDEX ( Filling_ID ) ,
CONSTRAINT FK_Order_Line FOREIGN KEY ( Filling_ID ) REFERENCES Filling( Filling_ID ) ON DELETE CASCADE ,
INDEX ( Bread_ID ) ,
CONSTRAINT FK_Order_Line FOREIGN KEY ( Bread_ID ) REFERENCES Bread( Bread_ID ) ON DELETE CASCADE ,
TYPE = INNODB );

 

[Alex H]

Try this (i'm no expert myself, but have adjusted a few things to look similar to a table of mine that works)

CREATE TABLE Order_Line
(Line_ID INT( 4 ) NOT NULL,
StOrd_ID INT( 4 ) NOT NULL,
Del_List_ID INT( 4 ) NOT NULL,
Filling_ID VARCHAR( 30 ) NOT NULL,
Bread_ID VARCHAR( 30 ) NOT NULL,
Quantity INT( 3 ) NOT NULL,
CONSTRAINT Pk_Order_Line PRIMARY KEY ( Line_ID ),
INDEX ( StOrd_ID ),
CONSTRAINT FK_Order_Line FOREIGN KEY ( StOrd_ID ) REFERENCES Standing_Order( StOrd_ID ) ON DELETE CASCADE,
INDEX ( Del_List_ID ),
CONSTRAINT FK_Order_Line FOREIGN KEY ( Del_List_ID ) REFERENCES Delivery_List( List_ID ) ON DELETE CASCADE,
INDEX ( Filling_ID ),
CONSTRAINT FK_Order_Line FOREIGN KEY ( Filling_ID ) REFERENCES Filling( Filling_ID ) ON DELETE CASCADE,
INDEX ( Bread_ID ),
CONSTRAINT FK_Order_Line FOREIGN KEY ( Bread_ID ) REFERENCES Bread( Bread_ID ) ON DELETE CASCADE)
ENGINE = INNODB;

 

[clio_chris]

Still no luck

Getting '#1005 - Can't create table '/Order_Line.frm' (errno: 150)'

I really do appreciate the help alot mate, thanks for giving it a look

 

[Alex H]

Hmm, try deleting the CONSTRAINT (~~~~) bit and just go with the FOREIGN KEY bit. I don't really know what to suggest, other than to check that all the tables and columns you are referencing exist, and are of exactly the same type, length and spelling. Alternatively paste in all of your "create tables" then can re-create the tables at this end.

 

[clio_chris]

Ah thankyou for giving it a look mate, I appreciate it, Ill give the rest of the tables etc a look and see what happens

Thanks again for your help

 

[18tooo]

just having a quick look over this, not too sure about MySQL as i dont use it much, but one thing i notice is that you seem to have 3 constraints all called FK_Order_Line. Constraint names have to be unique within a schema.

also, Filling_ID and Bread_ID being VARCHARs???

hope this is of some help

 

[clio_chris]

Ah i've tried renaming the constraint names and still get the same error

Yeah the VARCHAR bits need to be changed but ive left them all as that for now until i get this bit up and running.

 

[18tooo]

urmm, another idea: the fields youre referencing in the other tables, are they defined/constrained as UNIQUE especially the VARCHAR ones - cant reference a potentially non-unique field as a foreign key.

 

[clio_chris]

Thank all you guys for your help with this, I really do appreciate it.

Finally managed to get it to work last night, changed the FK labels so they werent all the same and hey presto

Thanks alot again guys!

Chris