I am using MySQL 8 innoDB.

I have an empty table and am trying to add a foreign key constraint. The parent table is large and gets a lot of fast queries.

ALTER TABLE my_table ADD CONSTRAINT xx FOREIGN KEY (parent_id) REFERENCES parent_table(id);

I get that adding a foreign key requires a lock on the parent table. What I don’t get, is why it doesn’t lock the tables, quickly add the key (the table is empty!), and then release the lock. Instead, everything locks up. Show processlist shows that the alter statement is waiting for the lock, and so are all the parent queries that are quickly piling up. No query actually seems to have the lock. I end up having to kill the alter statement.

waiting for table metadata lock

At this point, I think I’ll just add an index instead of a foreign key, but I hate to handle it with a hack like that. Any way around this?

(I have seen some similar questions, but they’re like 10 years old and don’t have any solutions.)