What is the error with this SQL insert statement?

Question

CREATE TABLE `api_key` (
  `id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `platform` char(2) NOT NULL,
  `key_type` char(1) NOT NULL,
  `key` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT  INTO api_key (key,key_type,platform,user_id,id) values ('123', '0', '00', '123', 'c89116d2');

Always output:

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 'key,key_type,platform,user_id,id) values ('123', '0', '00', '123', 'c89116d2')' at line 1

id column should be integer and use backticks for reserve kewords — Ankit Agrawal
– Ankit Agrawal, Commented Dec 12, 2016 at 6:17
@AnkitAgrawal But that still doesn't explain the error AFAIK. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 12, 2016 at 6:18

Tim Biegeleisen · Accepted Answer · 2016-12-12 06:17:46Z

1

key is a reserved keyword in MySQL, and you should avoid using it as a name for columns, tables, etc. (read: don't use if at all possible). However, you can get around this problem by escaping the column name key with backticks. Try the following INSERT:

INSERT INTO api_key (`key`, `key_type`, `platform`, `user_id`, `id`)
VALUES ('123', '0', '00', '123', 'c89116d2');

Note that I escaped all columns in backticks for consistency, though only the key column should require it to make the INSERT work.

answered Dec 12, 2016 at 6:17

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

What is the error with this SQL insert statement?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related