-- Remove unique constraint when it's used by a foreign key
-- Run these steps one by one

-- STEP 1: Find the foreign key constraint name
-- Run this to see all foreign keys on the bids table:
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'bids'
  AND COLUMN_NAME IN ('inquiryId', 'supplierId')
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- STEP 2: Find the index name (should be the same as constraint name or similar)
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId') AND Non_unique = 0;

-- STEP 3: Drop the foreign key constraint first
-- Replace 'FK_CONSTRAINT_NAME' with the actual CONSTRAINT_NAME from Step 1
-- Common names: 'bids_ibfk_1', 'bids_ibfk_2', 'bids_inquiryId_fk', etc.
-- ALTER TABLE `bids` DROP FOREIGN KEY `FK_CONSTRAINT_NAME`;

-- STEP 4: Drop the unique index
-- Replace 'INDEX_NAME' with the Key_name from Step 2
-- ALTER TABLE `bids` DROP INDEX `INDEX_NAME`;

-- STEP 5: Recreate foreign key constraints (if needed - usually not needed for these columns)
-- The foreign keys are usually on inquiryId -> inquiries.id and supplierId -> suppliers.id
-- These don't need to be unique, so we can recreate them as regular foreign keys

-- STEP 6: Create non-unique index
CREATE INDEX `idx_bids_inquiry_supplier` ON `bids` (`inquiryId`, `supplierId`);

-- STEP 7: Verify
SHOW INDEX FROM `bids` WHERE Column_name IN ('inquiryId', 'supplierId');
-- Should show Non_unique = 1