How to Fix Error 1064 When Using ALTER TABLE ADD CONSTRAINT in MySQL

1. Overview

MySQL’s Error 1064 (42000) when using the ALTER TABLE … ADD CONSTRAINT statement could be tricky to fix. It provides very sketchy information regarding the error itself, only directing the user to check the syntax without telling what the syntax issue is.

In this tutorial, we’ll learn how to troubleshoot this error.

2. Example Constraint

At the outset, we could get the error when adding any constraint, like PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK.

To demonstrate, we’ll use tables from Baeldung’s University database. We’ll use the FOREIGN KEY constraint called specification_program_id_fkey in the Specification table. Let’s first drop the foreign key constraint so that we can add it to demonstrate fixing the error 1064:

ALTER TABLE Specification 
DROP FOREIGN KEY specification_program_id_fkey;

What follows will be various ways we can incorrectly add a foreign key constraint.

For reference, recall that this is the correct syntax:

ALTER TABLE Specification
ADD CONSTRAINT specification_program_id_fkey 
FOREIGN KEY (program_id)
REFERENCES Program (id);

Now, we’re all set to explore some of the scenarios in which we could get the error, and how to identify the syntax issue.

3. Using Jumbled Syntax

Sometimes, we’re sure we remember the syntax and use it without double-checking it. For example, we could use a jumbled syntax like putting keywords in the wrong place:

ALTER TABLE Specification
ADD FOREIGN KEY specification_program_id_fkey 
CONSTRAINT (program_id)
REFERENCES Program (id);

This statement, however, will generate an error message:

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 'CONSTRAINT(program_id)
    REFERENCES program (id)' at line 4

We can fix the ERROR 1064 (42000) by using the correct syntax listed earlier.

4. Using Wrong Punctuation

We should use correct punctuation and avoid adding commas and parentheses that aren’t needed. An example of using wrong punctuation is adding enclosing parentheses:

ALTER TABLE Specification (
ADD CONSTRAINT
  specification_program_id_fkey 
  FOREIGN KEY (program_id)
  REFERENCES Program (id)
);

This statement, however, will generate the same error message:

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 '(ADD CONSTRAINT ...' at line 1

Again, the error message is ambiguous; however, we can use it to fix the common mistake in the syntax.

5. Using a Reserved Word

We shouldn’t use MySQL reserved words and keywords in our constraint names. For example, we might try to use the reserved word “foreign” as an identifier:

ALTER TABLE Specification
ADD CONSTRAINT foreign 
FOREIGN KEY (program_id)
REFERENCES Program (id);

This statement, however, will generate an error message:

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 'foreign ...' at line 2

Notably, the error message does direct us to check the syntax near ‘foreign’; however, it doesn’t tell us that we’re incorrectly using a reserved word.

6. Using Misspelled Keywords

We should double-check the spelling of the keywords before using them. As an example, using REFERENCE instead of the correctly spelled REFERENCES will generate the Error 1064:

ALTER TABLE Specification
ADD CONSTRAINT specification_program_id_fkey 
FOREIGN KEY (program_id)
REFERENCE Program (id);

7. Using Mismatched Parentheses

We should use well-matched parentheses. As an example, not using a closing parenthesis corresponding to an opening parenthesis will generate the Error 1064:

ALTER TABLE Specification
ADD CONSTRAINT specification_program_id_fkey 
FOREIGN KEY (program_id
REFERENCES Program (id);

8. Using Improper Quotes

We can optionally quote identifiers in MySQL. However, we can’t use single quotes (‘) or double quotes (“) to quote identifiers because by default, the identifier quote character is the backtick (`). As an example, if we were to use double-quotes without the ANSI_QUOTES SQL mode enabled, we’d get the Error 1064:

ALTER TABLE "Specification"
ADD CONSTRAINT "specification_program_id_fkey" 
FOREIGN KEY ("program_id")
REFERENCES Program ("id");

To avoid the error, we shouldn’t use quotes at all, or we should use backticks (“), as an example:

ALTER TABLE `Specification`
ADD CONSTRAINT `specification_program_id_fkey` 
FOREIGN KEY (`program_id`)
REFERENCES Program (`id`);

9. Missing Required Elements

We should use all of the required elements in the syntax. Let’s say we forget the REFERENCES clause:

ALTER TABLE Specification
ADD CONSTRAINT specification_program_id_fkey
FOREIGN KEY (program_id);

Since we’re missing a clause, it also generates an 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 near '' at line 4

As before, comparing the statement with the correct syntax is the first thing to do.

10. Conclusion

In this article, we learned about the different scenarios in which we could get the ERROR 1064 (42000) and how to fix it. This is by no means an exhaustive listing of error conditions for the error. Further, we’re usually not able to derive detailed information about what is causing the error from the error message, other than that it relates to incorrect use of syntax. Therefore, we should check and fix the syntax when we get this error message.