What's new
By:
ClioSport.net

This is a sample guest message. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

  • When you purchase through links on our site, we may earn an affiliate commission. Read more here.

SQL

clio_chris

clio_chris

Car  Renault Clio 1.2 16v
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.
 
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
 
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
 
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 );
 
Try this (i'm no expert myself, but have adjusted a few things to look similar to a table of mine that works)

Code: 
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;
 
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 :)
 
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.
 
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 :)
 
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
 
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.
 
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.
 
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 :D

Thanks alot again guys!

Chris
 
You must log in or register to reply here.
Back
Top