Dropping an unused foreign key constraint without table locks

Dropping foreign keys in MySQL requires care. Many developers assume dropping the constraint drops the underlying index automatically, or that the alter operation is safe on large tables.

-- Step 1: Drop the foreign key constraint (INPLACE, fast metadata operation):
ALTER TABLE order_items DROP FOREIGN KEY fk_order_items_order_id, ALGORITHM=INPLACE, LOCK=NONE;

-- Note: The underlying index `idx_order_items_order_id` is NOT dropped.
-- Step 2: Drop the index separately if it is truly no longer needed for queries:
-- Make it invisible first to test!
ALTER TABLE order_items ALTER INDEX idx_order_items_order_id INVISIBLE;

Specifying ALGORITHM=INPLACE, LOCK=NONE ensures MySQL will abort immediately rather than locking the table if an incompatible lock level is requested by the storage engine.