Drizzle Team•2mo ago

Orphaned Children tables after a successful migration

I was able to bypass sqlite constraint error by using PRAGMA foreign_keys option but it left my database in a corrupted state where children tables of ticket_order are referring to the temporary and now deleted ticket_order_old table

PRAGMA foreign_keys=off;--> statement-breakpoint
ALTER TABLE `ticket_order` RENAME TO `ticket_order_old`;--> statement-breakpoint
CREATE TABLE `ticket_order` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `nano_id` text NOT NULL,
    `event_id` text NOT NULL,
    `customer_id` text NOT NULL,
    `checkout_session_id` text NOT NULL,
    `order_status` text NOT NULL,
    `currency` text NOT NULL,
    `total_price` real NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now')),
    FOREIGN KEY (`customer_id`) REFERENCES `customer`(`id`) ON UPDATE no action ON DELETE no action
);--> statement-breakpoint
CREATE UNIQUE INDEX `ticket_order_nano_id_unique` ON `ticket_order` (`nano_id`);--> statement-breakpoint

INSERT INTO ticket_order (nano_id, event_id, customer_id, checkout_session_id, order_status, currency, total_price, created_at, updated_at)
SELECT id, event_id, customer_id, checkout_session_id, order_status, currency, total_price, created_at, updated_at
FROM ticket_order_old;--> statement-breakpoint

DROP TABLE `ticket_order_old`;--> statement-breakpoint
PRAGMA foreign_keys=on;

PRAGMA foreign_keys=off;--> statement-breakpoint
ALTER TABLE `ticket_order` RENAME TO `ticket_order_old`;--> statement-breakpoint
CREATE TABLE `ticket_order` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `nano_id` text NOT NULL,
    `event_id` text NOT NULL,
    `customer_id` text NOT NULL,
    `checkout_session_id` text NOT NULL,
    `order_status` text NOT NULL,
    `currency` text NOT NULL,
    `total_price` real NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now')),
    FOREIGN KEY (`customer_id`) REFERENCES `customer`(`id`) ON UPDATE no action ON DELETE no action
);--> statement-breakpoint
CREATE UNIQUE INDEX `ticket_order_nano_id_unique` ON `ticket_order` (`nano_id`);--> statement-breakpoint

INSERT INTO ticket_order (nano_id, event_id, customer_id, checkout_session_id, order_status, currency, total_price, created_at, updated_at)
SELECT id, event_id, customer_id, checkout_session_id, order_status, currency, total_price, created_at, updated_at
FROM ticket_order_old;--> statement-breakpoint

DROP TABLE `ticket_order_old`;--> statement-breakpoint
PRAGMA foreign_keys=on;

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Orphaned Children tables after a successful migration