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
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);"
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.