Why won't my query let me assign primary key?

Question

Just a note, I'm pretty new to SQL.

I'm using MySQL and using the app SequelPro.

I receive this error when I try to make a table:

"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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8' at line 4"

This is the code I'm using to make my table:

CREATE TABLE klout_scores_3 (
    id INT(11) NOT NULL AUTO_INCREMENT,
    score INT(11)
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You'll avoid some frustration if you keep this in mind: MySQL syntax errors that mention *"the right syntax to use near '...'"* **always** mean that the thing inside the quotes represents the *beginning* of something unexpected, which is why the message says **"near"** -- not "at." The error is often *near but before* the text shown. Here, the parser thought you were saying `score INT(11) PRIMARY KEY`, which is an alternate way of specifying a primary key column, and in that light, `(id) )` blew its mind, but the query seemed valid (though it was misinterpreted) up until that point. — Michael - sqlbot, Sep 24 '17 at 02:44
Similarly, `...the right syntax to use near ''` means the parser made it all the way the end of the query, and considers that to be impossible. A query that throws exactly this error is `SELECT * FROM;`. Clearly, when the end is reached, the server realizes that there's something mandatory (like a table name) that's missing. — Michael - sqlbot, Sep 24 '17 at 02:49
that really gives me a deeper sense of the error message. before, I was largely ignoring them. thank you michael. — Sebastian, Sep 24 '17 at 04:14

score 3 · Answer 1 · answered Sep 23 '17 at 21:15

3

score INT(11)

You want a comma , at end of that line.

answered Sep 23 '17 at 21:15

J_H

17,926
4
24
44

Woah, yea, so simple. Thank you. – Sebastian Sep 23 '17 at 22:34
Do you know what this means? ENGINE=InnoDB DEFAULT CHARSET=utf8 – Sebastian Sep 23 '17 at 22:34
1

@Sebastian: MySQL has many "engines" for storing data. InnoDB is one type. It allows to auto-validate data so it can help eliminate problem garbage IN. DEFAULT CHARSET=utf8 tells the engine that when a string is saved, it's "encoded" in utf8, which is a way for international accented characters to be stored but leaves the Western characters intact. – Jacques Amar Sep 23 '17 at 23:00
It's really amazing that people take the time to help out new learners (like me). I hope to pay it forward in the future like you're doing now. Thanks Jacques. – Sebastian Sep 23 '17 at 23:19

score 1 · Answer 2 · answered Sep 23 '17 at 23:07

Your query is being interpreted like this:

CREATE TABLE klout_scores_3 (
    id INT(11) NOT NULL AUTO_INCREMENT,
    score INT(11) PRIMARY KEY
    (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The (id) part is really confusing to MySQL and it's raising an error right there. Although adding a , after the INT(11) part will fix it, a better solution is to move the declaration:

CREATE TABLE klout_scores_3 (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    score INT(11)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It's a little concerning that you have a table name like klout_scores_3, as that suggests you have N of these tables. Relational database design and database normalization rules strongly frowns on this, you should have a singular table with some kind of ..._id column to relate the scores to whatever record that 3 identifies.

Why won't my query let me assign primary key?

2 Answers2