skip to Main Content

I would like to seek your insights regarding the error I’m encountering with my postgresql commands.

Basically, what I want to achieve is to create a "booking" entity with one to one relationship to another table called "booking details". But flyway won’t migrate my schema with the following error:

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException:
Migration V0__Initial.sql failed
--------------------------------
SQL State  : 42S02
Error Code : 42102
Message    : Table "BOOKING_DETAILS" not found; SQL statement:
ALTER TABLE booking ADD CONSTRAINT FK_BOOKING_ON_BOOKING_DETAILS FOREIGN KEY (booking_details_id) REFERENCES booking_details (booking_entity_id) [42102-214]
Line       : 16
Statement  : ALTER TABLE booking ADD CONSTRAINT FK_BOOKING_ON_BOOKING_DETAILS FOREIGN KEY (booking_details_id) REFERENCES booking_details (booking_entity_id)

Here is my postgresql commands:

DROP SEQUENCE IF EXISTS booking_transaction_sequence;
DROP TABLE IF EXISTS booking;

CREATE SEQUENCE  IF NOT EXISTS booking_transaction_sequence START WITH 1000 INCREMENT BY 100;

CREATE TABLE booking (
  id BIGINT NOT NULL,
   booking_number VARCHAR(255),
   booking_status VARCHAR(255),
   processed_by VARCHAR(255),
   created_at TIMESTAMP WITHOUT TIME ZONE,
   booking_details_id BIGINT,
   CONSTRAINT pk_booking PRIMARY KEY (id)
);

ALTER TABLE booking ADD CONSTRAINT FK_BOOKING_ON_BOOKING_DETAILS FOREIGN KEY (booking_details_id) REFERENCES booking_details (booking_entity_id);

DROP SEQUENCE IF EXISTS booking_details_transaction_sequence;
DROP TABLE IF EXISTS booking_details;
CREATE SEQUENCE  IF NOT EXISTS booking_details_transaction_sequence START WITH 1000 INCREMENT BY 100;

CREATE TABLE booking_details (
  booking_entity_id BIGINT NOT NULL,
   sender_name VARCHAR(255),
   item_details VARCHAR(255),
   pickup_address VARCHAR(255),
   rider_name VARCHAR(255),
   delivery_address VARCHAR(255),
   cancellation_reason VARCHAR(255),
   CONSTRAINT pk_booking_details PRIMARY KEY (booking_entity_id)
);

ALTER TABLE booking_details ADD CONSTRAINT FK_BOOKING_DETAILS_ON_BOOKINGENTITY FOREIGN KEY (booking_entity_id) REFERENCES booking (id);

I would highly appreciate any inputs regarding this. Thank you.

I tried using the "extend" method on my BookingDetails entity to BookingEntity. This run my java springboot application but for some reason I can’t fetch data with internal error 500 in postman. So I change my sql commands with the one-to-one relationship mapping but I got the above errors.

2

Answers


  1. Chosen as BEST ANSWER

    Solution: put this command on the last part of the sql so that the two tables must be created first before it can be altered.

    "ALTER TABLE booking ADD CONSTRAINT FK_BOOKING_ON_BOOKING_DETAILS FOREIGN KEY (booking_details_id) REFERENCES booking_details (booking_entity_id);"


  2. Just out of interest – how you’re planning to insert new entries given the schema? In order to create booking you’d need to have booking_details created and vice versa.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search