syntax error...

ktsirig posted this at 17:15 — 3rd February 2007.

Joined: Oct 2005

Hello all,
I am trying to create a table using the following syntax:

mysql_query ("CREATE TABLE IF NOT EXISTS RETURN (
RETURN_ID INT(12) NOT NULL AUTO_INCREMENT,
RETURN_RENTAL_ID INT(12) NOT NULL,
RETURN_COPY_ID INT(12) NOT NULL,
RETURN_MOVIE_ID INT(12) NOT NULL,
RETURN_DATE DATE NOT NULL,
PRIMARY KEY (RETURN_ID),
CONSTRAINT RETURN_IBFK_1 FOREIGN KEY(RETURN_RENTAL_ID) REFERENCES RENTAL(RENTAL_ID),
CONSTRAINT RETURN_IBFK_2 FOREIGN KEY(RETURN_COPY_ID) REFERENCES COPY(COPY_ID),
CONSTRAINT RETURN_IBFK_3 FOREIGN KEY(RETURN_MOVIE_ID) REFERENCES MOVIE(MOVIE_ID))"
) || die(mysql_error());

I get the following error:
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 'RETURN ( RETURN_ID INT(12) NOT NULL AUTO_INCREMENT, RETURN_RENTAL_ID I' at line 1

I am trying for 3 hours now to find what the problem is... I have checked everything, all about the foreign keys I use etc, but nothing seems to work... I can't see what my syntax error is...
Any help is greatly appreciated...

JeevesBond posted this at 22:37 — 3rd February 2007.

He has: 3,956 posts

Joined: Jun 2002

Try this:

mysql_query ("CREATE TABLE IF NOT EXISTS `RETURN` (
RETURN_ID INT(12) NOT NULL AUTO_INCREMENT,
RETURN_RENTAL_ID INT(12) NOT NULL,
RETURN_COPY_ID INT(12) NOT NULL,
RETURN_MOVIE_ID INT(12) NOT NULL,
RETURN_DATE DATE NOT NULL,
PRIMARY KEY (RETURN_ID),
CONSTRAINT RETURN_IBFK_1 FOREIGN KEY(RETURN_RENTAL_ID) REFERENCES RENTAL(RENTAL_ID),
CONSTRAINT RETURN_IBFK_2 FOREIGN KEY(RETURN_COPY_ID) REFERENCES COPY(COPY_ID),
CONSTRAINT RETURN_IBFK_3 FOREIGN KEY(RETURN_MOVIE_ID) REFERENCES MOVIE(MOVIE_ID))"
) || die(mysql_error());

A cut-down version of that just worked for me on my local MySQL.

EDIT: It doesn't seem to like 'RETURN' being the table name, without the backticks ``. This is probably because 'RETURN' is a reserved word. It's used as part of MySQLs language (stored procedures/user defined functions) so it gets confused when you start calling a table by that name!

It's generally good practice to either name things so they won't cause a problem, e.g. APPNAME_RETURNS, or put backticks around all your names, or you could do both!

a Padded Cell our articles site!