Mark Blatnik Mark Blatnik - 3 months ago 7
SQL Question

Syntax error while creating CONSTRAINT in MySQL

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

Answer

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.