ALTER TABLE ... ADD CONSTRAINT fails with error 1064 syntax error
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use nearShort answer
Error 1064 on ADD CONSTRAINT is a plain syntax error. MySQL expects ADD CONSTRAINT, a name, FOREIGN KEY with columns in brackets, then REFERENCES with the parent table and columns. The usual slips are a missing part of that sequence or a reserved word used as a table or column name without backticks. Read the text after 'near' in the message.
Fix it fast
1Find out which cause you have
Run the statement alone and read the text after 'near'. The parser stops at the first token it cannot place, so the mistake is at or just before it.
ALTER TABLE child ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent (id);2Match what you see, then jump to its fix
- 1
near points at REFERENCES, at the column list or right after the constraint nameA part of the FOREIGN KEY clause is missing or in the wrong orderFix - 2
near points at a name such as order, group, key or intervalA table or column name is a reserved wordFix - 3
The statement parses but the server refuses to add the constraint, with a different error than 1064The syntax is right but the foreign key cannot be createdFix
- 1
What does the error look like?
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use nearThe message ends with the first words MySQL could not parse
Cause 1 of 3
A part of the FOREIGN KEY clause is missing or in the wrong order
1Check
The documented form is ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...) REFERENCES tbl_name (col_name, ...), with optional ON DELETE and ON UPDATE after it. Compare your statement with it piece by piece.
SHOW CREATE TABLE parent\G2Fix
Write all parts in that order, with the column lists in brackets.
ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id) REFERENCES parent (id)
ON DELETE CASCADE;3Verify
SHOW CREATE TABLE lists the constraint.
SHOW CREATE TABLE child\GConfirmed by dev.mysql.com
Cause 2 of 3
A table or column name is a reserved word
1Check
Reserved words are only allowed as identifiers when quoted. Words that are keywords but not reserved can be used without quotes, so a name that works in one place can fail in another.
SHOW TABLES;2Fix
Quote the identifier with backticks in the statement, or rename it.
ALTER TABLE `order`
ADD CONSTRAINT fk_order_user
FOREIGN KEY (user_id) REFERENCES `user` (id);3Verify
The statement runs without 1064.
SHOW CREATE TABLE `order`\GConfirmed by dev.mysql.com
Cause 3 of 3
The syntax is right but the foreign key cannot be created
1Check
Parent and child must use the same storage engine, columns must have similar types with the same size and sign, and a string column needs the same character set and collation. The referenced columns need an index.
SHOW CREATE TABLE parent\G
SHOW CREATE TABLE child\G2Fix
Make the types match, add the index on the parent column first, then add the constraint again.
ALTER TABLE parent ADD INDEX idx_parent_id (id);3Verify
Run the ADD CONSTRAINT statement again.
ALTER TABLE child ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent (id);Confirmed by dev.mysql.com
How do I stop it coming back?
- Keep one DDL file per migration and run it against a scratch database first.
- Avoid reserved words as table and column names.
- Name every constraint yourself so that later changes can drop it by name.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses to create a foreign key (error 1215)
ERROR 1005 (HY000): Can't create table (errno: 150 "Foreign key constraint is incorrectly formed")
Error 1215 is a generic label.
5 causes, each with a fixDatabases
The server could not parse an SQL statement (error 1064)
ERROR 1064 (42000): You have an error in your SQL syntax; ... near '' at line 1
Error 1064 means the parser stopped at a place where the statement made no sense to it.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fix