I’m working on a drone management system using PostgreSQL with the PostGIS extension. I have several tables including Drones, Flights, Waypoints, Telemetry, Events, Errors, and CameraData. I’m trying to insert dummy data into these tables, but I’m encountering a foreign key constraint violation when inserting into the Flights table.

Here is the SQL script I’m using to create the tables and insert the dummy data:

— Enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

— Create the Drones table
CREATE TABLE IF NOT EXISTS Drones (
drone_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
model TEXT NOT NULL,
firmware_version TEXT NOT NULL
);

— Drop the existing Flights table if needed
DROP TABLE IF EXISTS Flights CASCADE;

— Create the Flights table with geometry type and constraints
CREATE TABLE IF NOT EXISTS Flights (
flight_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
start_time TIMESTAMP WITH TIME ZONE NOT NULL,
end_time TIMESTAMP WITH TIME ZONE NOT NULL,
duration INTERVAL NOT NULL,
pilot_id BIGINT NOT NULL,
drone_id BIGINT NOT NULL,
start_location GEOMETRY(Point, 4326),
end_location GEOMETRY(Point, 4326),
CONSTRAINT check_india_start_location CHECK (ST_Contains(ST_GeomFromText(‘POLYGON((68.1868 6.5546, 68.1868 35.6745, 97.4026 35.6745, 97.4026 6.5546, 68.1868 6.5546))’, 4326), start_location)),
CONSTRAINT check_india_end_location CHECK (ST_Contains(ST_GeomFromText(‘POLYGON((68.1868 6.5546, 68.1868 35.6745, 97.4026 35.6745, 97.4026 6.5546, 68.1868 6.5546))’, 4326), end_location)),
FOREIGN KEY (drone_id) REFERENCES Drones (drone_id)
);

— Create the Waypoints table
CREATE TABLE IF NOT EXISTS Waypoints (
waypoint_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
flight_id BIGINT NOT NULL,
latitude DOUBLE PRECISION NOT NULL,
longitude DOUBLE PRECISION NOT NULL,
altitude DOUBLE PRECISION NOT NULL,
FOREIGN KEY (flight_id) REFERENCES Flights (flight_id)
);

— Create the Telemetry table
CREATE TABLE IF NOT EXISTS Telemetry (
flight_id BIGINT NOT NULL,
timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
altitude DOUBLE PRECISION NOT NULL,
speed DOUBLE PRECISION NOT NULL,
battery_level DOUBLE PRECISION NOT NULL,
location GEOMETRY(Point, 4326),
PRIMARY KEY (flight_id, timestamp),
FOREIGN KEY (flight_id) REFERENCES Flights (flight_id)
);

— Create the Events table
CREATE TABLE IF NOT EXISTS Events (
event_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
flight_id BIGINT NOT NULL,
timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
event_type TEXT NOT NULL,
description TEXT,
FOREIGN KEY (flight_id) REFERENCES Flights (flight_id)
);

— Create the Errors table
CREATE TABLE IF NOT EXISTS Errors (
error_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
flight_id BIGINT NOT NULL,
timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
error_type TEXT NOT NULL,
description TEXT,
FOREIGN KEY (flight_id) REFERENCES Flights (flight_id)
);

— Create the CameraData table
CREATE TABLE IF NOT EXISTS CameraData (
camera_event_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
flight_id BIGINT NOT NULL,
timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
event_type TEXT NOT NULL,
FOREIGN KEY (flight_id) REFERENCES Flights (flight_id)
);

— Insert dummy values into Drones table
INSERT INTO Drones (model, firmware_version) VALUES
(‘DJI Phantom 4’, ‘v1.2.3’),
(‘Parrot Anafi’, ‘v2.0.1’);

— Verify the insertion into Drones table
SELECT * FROM Drones;

— Insert dummy values into Flights table
INSERT INTO Flights (start_time, end_time, duration, pilot_id, drone_id, start_location, end_location) VALUES
(‘2023-07-01 10:00:00+05:30’, ‘2023-07-01 10:30:00+05:30′, ’00:30:00’, 1, 1, ST_SetSRID(ST_MakePoint(78.9629, 20.5937), 4326), ST_SetSRID(ST_MakePoint(78.9629, 21.5937), 4326)),
(‘2023-07-02 14:00:00+05:30’, ‘2023-07-02 14:45:00+05:30′, ’00:45:00’, 2, 2, ST_SetSRID(ST_MakePoint(77.9629, 19.5937), 4326), ST_SetSRID(ST_MakePoint(77.9629, 20.5937), 4326));

— Verify the insertion into Flights table
SELECT * FROM Flights;
When I run the script, I get the following error:

ERROR: 23503: insert or update on table “flights” violates foreign key constraint “flights_drone_id_fkey”
DETAIL: Key (drone_id)=(2) is not present in table “drones”.