MySQL Syntax Error

Question

This is my SQL Script

CREATE TABLE account_profile
(
accnt_id int NOT NULL ,
first_name varchar(255),
last_name varchar(255),
biography varchar(255),
date_joined DATE,
country varchar(255),
gender varchar(255),
screename varchar(255),
path varchar(255),
FOREIGN KEY (accnt_id) REFERENCES accounts(id)
)

it kept giving me this error SQL query:

CREATE TABLE ac

MySQL said:

#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 '' at line 1

What does this specifically mean? what should I do? from my point of view the script is quite okay,I just can't pin point where's the error

you code is correct may be your accounts table is not exist. — Nikson Kanti Paul
– Nikson Kanti Paul, Commented Jun 28, 2012 at 7:51
Do you run this code alone or with other stuff (before this one)? I guess you are probably missing something in the previous line, like a ; — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 28, 2012 at 7:58

Andomar · Accepted Answer · 2012-06-28 07:53:23Z

3

Make sure you're using the InnoDB engine. The other engines do not support foreign keys.

CREATE TABLE account_profile
(
...
) ENGINE = INNODB;

Also check if the column account_profile.accnt_id matches the data type of accounts.id exactly. The second column should have an index defined on it (a primary key will do.)

answered Jun 28, 2012 at 7:53

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mihaisimi Over a year ago

This is marked with phpMyAdmin, if you are using that tool it helps to run the show errors command:dev.mysql.com/doc/refman/5.0/en/show-errors.html

John Woo · Accepted Answer · 2012-06-28 08:00:57Z

3

I have tested your query and it works with me too. Do you have a query before creating the table account_profile? because if you do, try to check if the query before has been terminated by a semi-colon. like this:

Create table TableName
(
    -- fields list
);  -- <== don't forget this before creating another table again.

CREATE TABLE account_profile
(
    accnt_id int NOT NULL ,
    first_name varchar(255),
    last_name varchar(255),
    biography varchar(255),
    date_joined DATE,
    country varchar(255),
    gender varchar(255),
    screename varchar(255),
    path varchar(255),
    FOREIGN KEY (accnt_id) REFERENCES accounts(id)
);  -- <== and also this.

the error says near line 1.

answered Jun 28, 2012 at 8:00

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Zaheer Ahmed · Accepted Answer · 2013-02-19 19:41:53Z

2

Probably your table account does not exists :

FOREIGN KEY (accnt_id) REFERENCES accounts(id)

That's why you've got an error, the request is correct otherwise.

edited Feb 19, 2013 at 19:41

Zaheer Ahmed

28.7k12 gold badges77 silver badges113 bronze badges

answered Jun 28, 2012 at 7:49

zessx

68.9k29 gold badges139 silver badges166 bronze badges

1 Comment

Zaheer Ahmed Over a year ago

I have checked the query, its working fine and me also not have accounts table.

Collectives™ on Stack Overflow

MySQL Syntax Error

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related