PRAGMA foreign_keys = ON;

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master WHERE type IN ("table", "index", "trigger");
PRAGMA writable_schema = 0;
VACUUM;

CREATE TABLE "death" (
"caused_by_ship_id" INT,
"id" INT,
"note" TEXT,
PRIMARY KEY ("id"),
FOREIGN KEY ("caused_by_ship_id") REFERENCES "ship"("id")
);

CREATE TABLE "ship" (
"lost_in_battle" INT,
"id" INT,
"name" TEXT,
PRIMARY KEY ("id")
);





INSERT INTO "ship" VALUES ("1", "101", "HMS Victory");
INSERT INTO "ship" VALUES ("0", "102", "USS Constitution");
INSERT INTO "ship" VALUES ("1", "103", "Eastwind Cruiser");
INSERT INTO "ship" VALUES ("0", "104", "Black Pearl");
INSERT INTO "ship" VALUES ("1", "105", "Eastern Star");
INSERT INTO "ship" VALUES ("0", "106", "Sea Hawk");
INSERT INTO "ship" VALUES ("1", "107", "Northern East");
INSERT INTO "ship" VALUES ("0", "108", "Southern Cross");
INSERT INTO "death" VALUES ("101", "201", "Fell overboard near the East coast");
INSERT INTO "death" VALUES ("102", "202", "Injured during storm");
INSERT INTO "death" VALUES ("103", "203", "Hit by enemy fire in the East sector");
INSERT INTO "death" VALUES ("104", "204", "Accidental explosion");
INSERT INTO "death" VALUES ("105", "205", "Died of illness on Eastern voyage");
INSERT INTO "death" VALUES ("106", "206", "Lost at sea");
INSERT INTO "death" VALUES ("107", "207", "Eastward battle casualties");
INSERT INTO "death" VALUES ("108", "208", "Friendly fire incident");