FIX_FOREIGN_KEY.sql

-- 1. Drop existing constraint on rentals table
ALTER TABLE rentals 
DROP CONSTRAINT IF EXISTS rentals_item_id_fkey;

-- 2. Re-add constraint with ON DELETE CASCADE
ALTER TABLE rentals
ADD CONSTRAINT rentals_item_id_fkey
FOREIGN KEY (item_id)
REFERENCES items(id)
ON DELETE CASCADE;

-- 3. Also do the same for Notifications if they link to items
ALTER TABLE notifications
DROP CONSTRAINT IF EXISTS notifications_related_item_id_fkey;

ALTER TABLE notifications
ADD CONSTRAINT notifications_related_item_id_fkey
FOREIGN KEY (related_item_id)
REFERENCES items(id)
ON DELETE CASCADE;

ALTER TABLE notifications
DROP CONSTRAINT IF EXISTS notifications_related_rental_id_fkey;

ALTER TABLE notifications
ADD CONSTRAINT notifications_related_rental_id_fkey
FOREIGN KEY (related_rental_id)
REFERENCES rentals(id)
ON DELETE CASCADE;