Syntax error in SQL statement “CREATE TABLE TRIP…”

Question

I am having issues spoting the syntax error when trying to create a table using flyway and hibernate for PostgresSql. The Trip class has no relationship with other classes (yet). I have allready managed to successfully create for the other 2 classes Purchase and User, but this one just gives errors.

Error message:

[ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 3.003 s <<< FAILURE! - in org.studentnr.backend.service.UserServiceTest [ERROR] org.studentnr.backend.service.UserServiceTest.testCreateUser Time elapsed: 0.004 s <<< ERROR! java.lang.IllegalStateException: Failed to load ApplicationContext Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration V1.0__createDB.sql failed

SQL State : 42000 Error Code : 42000 Message : Syntax error in SQL statement "CREATE TABLE TRIP (ID BIGINT GENERATED BY DEFAULT AS IDENTITY, TITLE VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(255), COST INTEGER NOT NULL, LOCATION VARCHAR(124) NOT NULL, DEPARTURE DATE NOT NULL, RETURNING DATE NOT NULL, PRIMARY KEY (ID))

My flyway sql script:

create sequence hibernate_sequence start with 1 increment by 1;

create table user_roles (user_email varchar(255) not null, roles varchar(255));

create table users (email varchar(255) not null, firstname varchar(50) not null, middle_name 
varchar(50), surename varchar(50) not null, password varchar(255) not null, address 
varchar(128) not null, postal_code varchar(124) not null, enabled boolean not null, primary 
key (email));

create table purchase (id bigint generated by default as identity, booked_date date not 
null, user_email varchar(255) not null, trip_id bigint not null, primary key (id));

create table trip (id bigint generated by default as identity, title varchar(255) not null, 
description varchar(255), cost integer not null, location varchar(128) not null, departure 
date not null, returning date not null, primary key (id))


alter table user_roles add constraint FKs9rxtuttxq2ln7mtp37s4clce foreign key (user_email)         
references users;

alter table purchase add constraint FKlqrv1aj0pon999jbi5esfpe4k foreign key (user_email) 
references users;

Here is my Trip entity:

package org.studentnr.backend.entities;

import javax.persistence.*;
import javax.validation.constraints.*;
import java.time.LocalDate;

@Entity
public class Trip {

 @Id @GeneratedValue
 private Long id;

 @NotBlank
 @Size(max=255)
 private String title;

 //@NotBlank //TODO: Can be blank???
 @Size(max=255)
 private String description;

 //@Min(0) TODO: remember to add 'check (cost>0)' to flyway to avoid using negative values
 @NotNull
 private Integer cost;

 @NotBlank
 @Size(max = 124)
 private String location;

 @NotNull
 @Future   
 private LocalDate departure;

 @NotNull
 @Future  
 private LocalDate returning;



public Trip(){
}

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getTitle() {
    return title;
}

public void setTitle(String title) {
    this.title = title;
}

public String getDescription() {
    return description;
}

public void setDescription(String description) {
    this.description = description;
}

public int getCost() {
    return cost;
}

public void setCost(int cost) {
    this.cost = cost;
}

public String getLocation() {
    return location;
}

public void setLocation(String location) {
    this.location = location;
}

public LocalDate getDepartureDate() {
    return departure;
}

public void setDepartureDate(LocalDate departureDate) {
    this.departure = departureDate;
}

public LocalDate getReturnDate() {
    return returning;
}

public void setReturnDate(LocalDate returnDate) {
    this.returning = returnDate;
}



}

The error code pointed to MySql, which you could have mentioned btw. This online checker says, its caused by by default as identity. There's documentation available for mysql create table statements. — Nikolai Dmitriev
generated by default as identity is applicable in PostgreSQL, Oracle, ... but is not applicable in MySQL. Use id bigint auto_increment instead. PS. create sequence is not applicable in MySQL too. PPS. alter table .. add constraint .. foreign key (..) references users; is not applicable in MySQL too - columns definition must be provided for referencing explicitly. — Akina
I am using postgresSQL sorry for forgetting to mention that! updating post. — Roozbeh
Note that a limit of 255 has not performance or storage advantages over 250 or 275 if you expect that number to open up some magic optimizations in storage — a_horse_with_no_name
@a_horse_with_no_name I am just resuing examples from my teachers lectures. I have no idea why he picked 255 chars in some of his variables. What do you suggest? — Roozbeh

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2021-05-07T09:23:28

There are two errors in your script:

There is a ; missing at the end of the create trip statement.
returning is a reserved keyword. You will have to quote it "returning" date - but it would be easier in the long run if you find a different name.

If these two things are corrected, the script works

I would also use more meaningful names for the foreign key constraints.

Syntax error in SQL statement “CREATE TABLE TRIP…”

1 Answers