alter table query taking too long for adding constraint

Question

I want to alter a table called person and want to add foreign key to it using office table

the query I am using is

ALTER TABLE person
ADD CONSTRAINT person_Office_FK
FOREIGN KEY ( Office_id )
REFERENCES Office ( Office_id ) ;

Table office has around 500,000 rows and table person has around 5 million

This query is taking forever i am not sure what is happening.

Btw, I think this is somewhat expected since it needs to cross-check *a lot* of rows. — Patrick Hofman, Aug 07 '14 at 19:58
forever means i am running this query from past 45 minutes, and I am not sure what is going on and how long it is going to take.. — mb1987, Aug 07 '14 at 20:00
I don't know if MySQL has this, but in MS-SQL you can specify WITH NOCHECK which will create the Foreign Key without checking your existing data to make sure it conforms. It will only apply to future data. Which also means it won't take "forever". Right now your database is checking every row in your person table to make sure it has a matching Office_id in the Office table. — Tab Alleman, Aug 07 '14 at 20:01

score 4 · Answer 1 · edited May 23 '17 at 12:13

4

If Office_id is the primary key of Office, make sure it has a (primary key) index. This will definitely speed up the adding of the constraint.

Also, according to How to temporarily disable a foreign key constraint in MySQL?, you can use

SET FOREIGN_KEY_CHECKS=0;

To disable ALL foreign key constraint checks, possibly this works too when adding them.

edited May 23 '17 at 12:13

Community

1
1

answered Aug 07 '14 at 20:09

Patrick Hofman

153,850
22
249
325

score 2 · Answer 2 · answered Nov 22 '16 at 01:13

You can use:

SET FOREIGN_KEY_CHECKS=0;

I typically do my own foreign key checking before I do this, though:

SELECT
  Person.*
FROM person
LEFT JOIN Office ON (person.Office_id = Office.Office_id)
WHERE Office.Office_id IS NULL
;

That query should return nothing if you are safe to turn off the FOREIGN_KEY_CHECKS.

score 2 · Answer 3 · edited Mar 19 '20 at 12:06

The question is still not obsolete. I just got the same issue here and found a workaround in 5 steps:

Duplicate the table: CREATE TABLE table2 LIKE table1;
Apply the constraint foreign key: ALTER TABLE..FOREIGN.... bla bla bla
Dump data from table1 to table2: INSERT INTO table2 SELECT * FROM table1;
Delete table1: DROP TABLE table1;
Rename table2: RENAME TABLE table2 TO table1;

All in one:

CREATE TABLE table2 LIKE table1;
ALTER TABLE..FOREIGN.... bla bla bla;
INSERT INTO table2 SELECT * FROM table1;
DROP TABLE table1;
RENAME TABLE table2 TO table1;

score 1 · Accepted Answer · answered Aug 07 '14 at 20:18

Before adding your constraint, make sure that there's a clustered index on office_id on the office table and a non-clustered index on office_id on the person table.

Remember that every occurrence of office_id on the person table needs to check against every office_id record. This will also speed things up if you ever have to delete an office record.

You don't want to disable the checks, since your constraint will be untrusted and you won't get the performance benefit a foreign key gives you in the query optimizer.

MySQL does not have a concept of trusted/untrusted constraints, such as Microsoft SQL Server has (as of this date). — RasmusWL, Feb 27 '18 at 11:48

alter table query taking too long for adding constraint

4 Answers4