Syntax error when creating a foreign key constraint

Question

I am creating a table where it has a foreign key so that it would be linked to another table, but it kept me giving this error, I already checked the syntax on w3schools, but I still keep getting errors any idea why? here's my SQL script

CREATE TABLE user_profile
(
user_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
accnt_id INT,
first_name varchar(255),
last_name varchar(255),
biography TEXT,
date_joined DATETIME,
date_of_birth DATE,
email varchar(255),
gender varchar(255),
screenname varchar(255)
country varchar(255),
FOREIGN KEY (accnt_Id) REFERENCES accounts(accnt_Id)
)

Here's the error

#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 'country varchar(255), FOREIGN KEY (accnt_Id) REFERENCES accounts(accnt_Id) )' at line 13

a. does the accounts table have accnts_Id field. b. both the tables have InnoDB engine? — hjpotter92
– hjpotter92, Commented Jun 23, 2012 at 9:21
@T-ShirtDude the accounts table has an innoDB engine, this table I don't know since I am creating it manually through my script — KyelJmD
– KyelJmD, Commented Jun 23, 2012 at 9:25
The syntax error is before the constraint, right before "country" — Mat
– Mat, Commented Jun 23, 2012 at 9:29

Simon Dorociak · Accepted Answer · 2012-06-23 09:30:35Z

3

You are missing , behind screenname varchar(255) change it to

screenname varchar(255),

and it should works.

answered Jun 23, 2012 at 9:30

Simon Dorociak

33.5k10 gold badges70 silver badges107 bronze badges

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

2 Comments

Ryan Over a year ago

wouldn't it be in front of screename?

Simon Dorociak Over a year ago

no man, it must be how i wrote. screenname is not last column so then behind declaration of column must be comma.

Chetter Hummin · Accepted Answer · 2012-06-23 09:31:51Z

0

You are missing a comma after the 'screenname' column

CREATE TABLE user_profile
(
    user_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    accnt_id INT,
    first_name varchar(255),
    last_name varchar(255),
    biography TEXT,
    date_joined DATETIME,
    date_of_birth DATE,
    email varchar(255),
    gender varchar(255),
    screenname varchar(255),
    country varchar(255),
    FOREIGN KEY (accnt_Id) REFERENCES accounts(accnt_Id)
)

answered Jun 23, 2012 at 9:31

Chetter Hummin

6,8478 gold badges34 silver badges45 bronze badges

Collectives™ on Stack Overflow

Syntax error when creating a foreign key constraint

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related