Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

ALTER TABLE ... ADD CONSTRAINT fails with error 1064 syntax error

ErrorERROR 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 near

Short 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.

  • Applies to MySQL 8.4, MySQL 8.0
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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);
  2. 2Match what you see, then jump to its fix

    1. 1near 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. 2near points at a name such as order, group, key or intervalA table or column name is a reserved wordFix
    3. 3The 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

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 near
  • The 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\G

2Fix

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\G

Confirmed by dev.mysql.com

Did this fix it?

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`\G

Confirmed by dev.mysql.com

Did this fix it?

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\G

2Fix

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.