SQL syntax error on create query

Question

When I run query below on phpMyAdmin and Sequel, I got the following 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 'id Int UNSIGNED NOT NULL AUTO_INCREMENT, login Char(128) NOT NULL, passw' at line 3.

I can't figure out the error on this query. Mysql is running on 5.5.25 version.

CREATE TABLE user
(
  id Int UNSIGNED NOT NULL AUTO_INCREMENT,
  login Char(128) NOT NULL,
  password Char(128) NOT NULL,
  emailaddress Char(128),
  alternateemailaddress Char(128),
  officephone Char(20),
  officefax Char(20),
  mobilephone Char(20),
  client Int UNSIGNED,
  facility Int UNSIGNED,
  user_status Int UNSIGNED NOT NULL DEFAULT 0,
  valid_from Datetime NOT NULL,
  valid_to Datetime NOT NULL,
  last_login Datetime,
  last_login_from Char(48),
  modified_by Int NOT NULL,
  modified_time Datetime,

  PRIMARY KEY (id),
  FOREIGN KEY client REFERENCES client (id) ON DELETE CASCADE,
  FOREIGN KEY facility REFERENCES facility (id) ON DELETE CASCADE
 
 ) ENGINE = InnoDB
  AUTO_INCREMENT = 0
;

@TheUnlucky i think the error is cause by copying the query. there might some hidden characters or spaces. I tried copying the query from this post, but it still return an error, different error on this time on references. it is good now when i remove the foreign key. thanks for confirming the query. — Pelang
– Pelang, Commented Apr 15, 2013 at 4:30

Michael Sivolobov · Accepted Answer · 2013-04-15 04:30:38Z

1

You should use backsticks when writing names of tables and fields to avoid MySQL parse it as reserved keywords. You should also use parentheses wrapping your foreign keys names. Working query should look like this:

CREATE TABLE `user`
(
  `id` Int UNSIGNED NOT NULL AUTO_INCREMENT,
  `login` Char(128) NOT NULL,
  `password` Char(128) NOT NULL,
  `emailaddress` Char(128),
  `alternateemailaddress` Char(128),
  `officephone` Char(20),
  `officefax` Char(20),
  `mobilephone` Char(20),
  `client` Int UNSIGNED,
  `facility` Int UNSIGNED,
  `user_status` Int UNSIGNED NOT NULL DEFAULT 0,
  `valid_from` Datetime NOT NULL,
  `valid_to` Datetime NOT NULL,
  `last_login` Datetime,
  `last_login_from` Char(48),
  `modified_by` Int NOT NULL,
  `modified_time` Datetime,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`client`) REFERENCES `client` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`facility`) REFERENCES `facility` (`id`) ON DELETE CASCADE

 ) ENGINE = InnoDB
  AUTO_INCREMENT = 0
;

answered Apr 15, 2013 at 4:30

Michael Sivolobov

13.9k5 gold badges48 silver badges68 bronze badges

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

5 Comments

Pelang Over a year ago

thanks for the suggestions. i did delete the table i created to check if the query works with the references. when i copy and execute your query, I got this error "Can't create table 'portal.user' (errno: 150)."

Pelang Over a year ago

is it alright to add foreign key when the reference table is not yet created?

Michael Sivolobov Over a year ago

I think that your id field in client and facility doesn't have UNSIGNED attribute. Try to remove UNSIGNED from client and facility fields in my variant of your create query and you'll see that it will work.

Bohemian Over a year ago

back ticks are not necessary for this query - see my sqlfiddle proving it.

Michael Sivolobov Over a year ago

I don't say that backsticks are necessary but it should be used everywhere in field and table names to avoid some problems with reserved words. Mainly, problem was in wrong type provided by fields in referenced tables.

Bohemian · Accepted Answer · 2013-04-15 04:39:50Z

1

This works for me.
See my successful SQLFiddle

I suspect you have some odd whitespace characters in your input, perhaps tabs.

answered Apr 15, 2013 at 4:39

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

1 Comment

Bohemian Over a year ago

The query part is empty, but the schema (the table create) builds OK.

Collectives™ on Stack Overflow

SQL syntax error on create query

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related