CODEWITHSUNDEEP
mysqlmysql-workbench
James Yeoman
James Yeoman

Reputation: 675

Error 1064 in MySQL Workbench when synchronising a model to a server

I am new to MySQL and have only used Microsoft Access before for SQL. When I try to add the Model to the server ([Database]->[Synchronize Model]), the auto-generated SQL code throws this error

Executing SQL script in server
ERROR: Error 1064: 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 ')

REFERENCES `medicineproblems`.`SleepTimes` ()
    ON DELETE NO ACTION
    ' at line 7
SQL Code:
        CREATE TABLE IF NOT EXISTS `medicineproblems`.`Records` (
          `Date` DATE NOT NULL,
          `SleepDuration` DECIMAL NOT NULL DEFAULT 0,
          `MoodAverage` DECIMAL NOT NULL DEFAULT 0,
          PRIMARY KEY (`Date`),
          CONSTRAINT `SleepDuration`
            FOREIGN KEY ()
            REFERENCES `medicineproblems`.`SleepTimes` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `MoodAverage`
            FOREIGN KEY ()
            REFERENCES `medicineproblems`.`Mood` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8

SQL script execution finished: statements: 5 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

I want to be able to know what went wrong. So in your answers, can you please tell me possible causes of the error so I can aim to avoid it?

Upvotes: 0

Views: 148

Answers (1)

Rahul
Rahul

Reputation: 77926

You are missing the column name to reference on that table as well as the column which will be referring to and thus the error is pointing about. The below line

        FOREIGN KEY ()
        REFERENCES `medicineproblems`.`SleepTimes` ()

Should be

FOREIGN KEY(some_column_name)
REFERENCES `medicineproblems`.`SleepTimes`(some_column_name)

Similarly you should correct this in the below part of your CREATE TABLE syntax

      CONSTRAINT `MoodAverage`
        FOREIGN KEY ()
        REFERENCES `medicineproblems`.`Mood` ()

See MySQL Documentation for more information. Correct syntax is:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

Upvotes: 1

Related Questions