Issues with MySQL code?

Question

I"m getting an error somewhere around PRIMARY KEY but can't figure out what exactly it is, can someone take a look?

CREATE TABLE `reports` (

    `key`                   INT UNSIGNED AUTO_INCREMENT, 
    `role`                  VARCHAR(70), 
    `region`                VARCHAR(70),
    `inspection_type`       VARCHAR(70),
    `inspection_number`     VARCHAR(70),
    `customer_number`       VARCHAR(70),

    `report_date`           DATE DEFAULT NULL,  



    `order_date`            DATE DEFAULT NULL,  


    `customer`              VARCHAR(70),
    `customer_division`     VARCHAR(70),
    `location`              VARCHAR(70),
    `memo`                  VARCHAR(255),       
    `billingkey`            VARCHAR(70),

    PRIMARY KEY(key)

) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

If it isn't to late to rethink your column name, you ought to pick something other than `key` to save yourself more trouble later on. — Michael Berkowski, Feb 09 '12 at 22:02
@micharl It can't be helped, it's a client requirement, not something I can negotiate with... =( — ehime, Feb 10 '12 at 18:22
possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — Ian Ringrose, May 06 '14 at 10:17

score 6 · Accepted Answer · answered Feb 09 '12 at 21:57

6

Enclose key in backquotes. It is a MySQL reserved keyword.

PRIMARY KEY(`key`)

answered Feb 09 '12 at 21:57

Michael Berkowski

267,341
46
444
390

+1 Better yet, don't name your column key if possible. Too much potential for future errors here IMO. – Michael Mior Feb 09 '12 at 21:58

score 2 · Answer 2 · answered Feb 09 '12 at 21:57

2

key is a reserved word. Technically, you'd have to do:

PRIMARY KEY(`key`)

to use it, but don't call your primary key key.

answered Feb 09 '12 at 21:57

WWW

9,734
1
29
33

+1 Because you were typing it as a comment at the same time I was putting an answer... – Michael Berkowski Feb 09 '12 at 22:00
I lookup the list so often for SO answers that when I start typing `my` in Chrome, the MySQL reserved keywords list pops up immediately. – Michael Berkowski Feb 09 '12 at 22:00

score 0 · Answer 3 · answered Feb 09 '12 at 21:58

0

You need to escape the word key, it's a reserved keyword:

.
.
.     
PRIMARY KEY(`key`)

) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

answered Feb 09 '12 at 21:58

Ben English

3,900
2
22
32

Issues with MySQL code?

3 Answers3