Unexpected Token = Rookie needs Syntax help

[UncleRobin]

I am trying to create a table in SQL. It’s a pretty straightforward table, and I could use Design View, but I’m trying to learn more about SQL. When I try, I get “unexpected token in statement” error. I’m sure this is a typo, a syntax error or both, but can’t figure out exactly WHAT I have done wrong. Here’s the code I’m trying to execute:

CREATE TABLE “Movies”(
“Movie-ID”
INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)
NOT NULL
PRIMARY KEY

,"TITLE"     
  	VARCHAR (255)
  	NOT NULL
,"YEAR"
    SMALLINT (5)
, "YEAR WATCHED"
    SMALLINT (5)
, "SCORE"
   DECIMAL	(4,2)
,"Country" 
  	Integer	REFERENCES 
 	"Countries" ("Country-ID")
,"LANGUAGE"
    VARCHAR (255)	
	
 )
 ;

The table and column names referenced in the FK are both correct, fwiw.

Where have I gone wrong here?

[RobertG]

SMALLINT doesn’t expect a length. You have added (5) here. Have a look at INTEGER, wich also doesn’t expect a length and is written right here.

[Ratslinger]

Hello,

REFERENCES will also give an error. This works:

CREATE TABLE "Movies" ("Movie-ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
                         "TITLE" VARCHAR (255) NOT NULL,
                         "YEAR" SMALLINT,
                         "YEAR WATCHED" SMALLINT,
                         "SCORE"   DECIMAL	(4,2),
                         "Country" Integer,
                        "LANGUAGE" VARCHAR (255),
                                 FOREIGN KEY ("Country") REFERENCES "Countries" ("Country-ID") );

Based upon previous question(s) you are using HSQLDB embedded. Reference manual → Hsqldb User Guide

[UncleRobin]

Thank you both! I had removed the (5) from SMALLINT and discovered the REFERENCES error. One of those SQL “dialect” issues obviously That HSQLDB userguide will be invaluable, at least until I’ve learned enough about SQL to move beyond using it.