26 lines
1014 B
PL/PgSQL
26 lines
1014 B
PL/PgSQL
-- Clear messages and leases, then update messages.lease_id FK to ON DELETE SET NULL.
|
|
-- Run with: psql "<connection string>" -f scripts/clear-messages-leases-then-fix-fk.sql
|
|
-- WARNING: Permanently deletes all rows in messages and leases (and lease_documents).
|
|
-- Unlinks lease_id in tenant_invites, payment_transactions, and reminders.
|
|
|
|
BEGIN;
|
|
|
|
-- 1. Clear messages (references leases)
|
|
DELETE FROM messages;
|
|
|
|
-- 2. Unlink or remove other tables that reference leases so we can delete leases
|
|
DELETE FROM lease_documents;
|
|
UPDATE tenant_invites SET lease_id = NULL;
|
|
UPDATE payment_transactions SET lease_id = NULL;
|
|
UPDATE reminders SET lease_id = NULL;
|
|
|
|
-- 3. Clear leases
|
|
DELETE FROM leases;
|
|
|
|
-- 4. Update constraint so future lease deletes don't block on messages
|
|
ALTER TABLE messages DROP CONSTRAINT IF EXISTS messages_lease_id_leases_id_fk;
|
|
ALTER TABLE messages ADD CONSTRAINT messages_lease_id_leases_id_fk
|
|
FOREIGN KEY (lease_id) REFERENCES public.leases(id) ON DELETE SET NULL ON UPDATE NO ACTION;
|
|
|
|
COMMIT;
|