Mysql Syntax error on creating table

Question

I have a MySQL database inside a docker container. The entrypoint executes a script creating the database and a script creating tables.

Everything works fine except for one table.

use db;

... creating other tables

CREATE TABLE mediametadata
(
id bigint not null  primary key,
title VARCHAR not NULL,
artist VARCHAR not NULL,
album VARCHAR,
releaseYear bigint,
mediaId bigint NOT NULL,
constraint fk__pk_metadata_objectid
        foreign key (id) references objectid (id),

constraint fk__metadata_media_id
        foreign key (mediaId) references media (id)
)
;

The error logged to console is

mysql_1  | ERROR 1064 (42000) at line 150: 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 'not NULL,
mysql_1  | artist VARCHAR not NULL,
mysql_1  | album VARCHAR,
mysql_1  | releaseYear bigint,
mysql_1  | mediaId bi' at line 4

line 150 is the open bracket after CREATE TABLE mediametadata

MySQL version: 8.0.3-rc-log
Host OS: Debian jessie

I came to think year and metadata might be reserved keywords so I renamed the table and the year column but the error persists.

You need to give every VARCHAR a length. I'm pretty sure I've answered this question on Stack Overflow before. — Bill Karwin
– Bill Karwin, Commented Jan 4, 2018 at 16:39
@Bill Karwin is right. Just change VARCHAR to VARCHAR(length). — tanktoo
– tanktoo, Commented Jan 4, 2018 at 16:42
while your at it, dont create primary keys like this (unnamed) but name them just like you did with your foreign keys — GuidoG
– GuidoG, Commented Jan 4, 2018 at 16:49
thanks a lot ... too obvious did it for all the other tables forgot to set the varchar length here — Kevin Kussmaul
– Kevin Kussmaul, Commented Jan 4, 2018 at 16:58
@GuidoG, MySQL doesn't support naming primary keys. They are all called PRIMARY. Try it: create table foo ( id int, constraint mypk primary key (id)); followed by show create table foo and you won't see "mypk" anywhere. — Bill Karwin
– Bill Karwin, Commented Jan 4, 2018 at 17:01

Kevin Kussmaul · Accepted Answer · 2018-01-04 17:06:16Z

2

answered by @Bill Karwin in first comment Varchar needs length [Varchar(number)]

answered Jan 4, 2018 at 17:06

Kevin Kussmaul

2803 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

Mysql Syntax error on creating table

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