Syntax error while creating CONSTRAINT in MySQL

Question

The first table was created with no problems:

mysql > CREATE TABLE nodes(
     -> id varchar(10) PRIMARY KEY,
     -> user text,
     -> uid varchar(10),
     -> version tinyint,
     -> changeset smallint,
     -> timestamp timestamp
     -> );

It is when I tried to create the second table that MySQL is outputting an error:

mysql > CREATE TABLE node_tags(
     -> id varchar(10),
     -> key text,
     -> value text,
     -> type text,
     -> CONSTRAINT pk_node_tag PRIMARY KEY (id, key),
     -> CONSTRAINT fk_node_tags_id FOREIGN KEY (id)
     ->  REFERENCES nodes (id)
     -> );

ERROR 1064 (42000): 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 ' value text, type text, CONSTRAINT pk_node_tag PRIMARY KEY (id, key), CONSTRAINT' at line 3

Drew · Accepted Answer · 2016-08-20 04:11:21Z

4

This succeeds. Key is a reserved word, so it needs back-ticks.

Also, a text in an index limits it size as to how much of it can be indexed. It is influenced by your character set in force.

So the following runs thru:

drop table if exists nodes;
CREATE TABLE nodes
(   id varchar(10) PRIMARY KEY,
    user text,
    uid varchar(10),
    version tinyint,
    changeset smallint,
    timestamp timestamp
)engine=innodb;

drop table if exists node_tags;
CREATE TABLE node_tags
(   id varchar(10) not null,
    `key` text,
    value text,
    type text,
    CONSTRAINT pk_node_tag PRIMARY KEY (id, `key`(255)),
    CONSTRAINT fk_node_tags_id FOREIGN KEY (id) REFERENCES nodes (id)
)engine=innodb;

I would highly suggest not having a TEXT in a primary key anyway.

answered Aug 20, 2016 at 4:11

Drew

25k10 gold badges47 silver badges81 bronze badges

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

2 Comments

user6375332 Over a year ago

Great, this went through. Thank you for your help.

Drew Over a year ago

Your welcome. I am sorry I can't recommend something about the key as I know little about it. You know best with your data. Remember it is going to be way-wide so if you use it as a foreign key you may run into performance problems (certainly compared to a thin INT)

Ankit Bhatnagar · Accepted Answer · 2016-08-20 05:00:00Z

0

Reserved keywords such as 'key, value, timestamp etc' shouldn't be preferred while creating columns. This induces bugs and isn't scalable. As far as this is concerned, using backticks key will solve the issue here.

answered Aug 20, 2016 at 5:00

Ankit Bhatnagar

7555 silver badges16 bronze badges

Comments

sia · Accepted Answer · 2017-06-09 21:02:40Z

0

because key is the reserved word in MySQL. please refer to this document to see list of the reserved words: https://dev.mysql.com/doc/refman/5.5/en/keywords.html

answered Jun 9, 2017 at 21:02

sia

5891 gold badge9 silver badges24 bronze badges

Collectives™ on Stack Overflow

Syntax error while creating CONSTRAINT in MySQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related