Create table with foreign key syntax error

Question

I'm trying to make 3 tables using sql queries in node.js

the first 2 queries works fine and creating the tables. but the last one has a syntax error but I don't know what is wrong...

connection.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");
    const queryUserMgmtTable = "CREATE TABLE if not exists user_mgmt (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), surname VARCHAR(50), email VARCHAR(50), birth_date DATETIME)";
    connection.query(queryUserMgmtTable, function (err, result) {
        if (err) throw err;
        console.log("Table user_mgmt created");
    });

    const queryTaskMgmtTable = "Create table if not exists task_mgmt(id int Primary key NOT NULL, name Varchar(50), Result ENUM('created', 'in_progress', 'done') NOT NULL)";
    connection.query(queryTaskMgmtTable, function (err, result) {
        if (err) throw err;
        console.log("Table task_mgmt created");
    });

// something is wrong with the foreign key here:
    const queryUser_taskTable = "Create table if not exists user_task(UserId int FOREIGN KEY REFERENCES user_mgmt(id), UserId int FOREIGN KEY REFERENCES task_mgmt(id))";
    connection.query(queryUser_taskTable, function (err, result) {
        if (err) throw err;
        console.log("Table user_task created");
    });
});

The error is the following:

sqlMessage: "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 KEY REFERENCES 'user_mgmt(id)', UserId int FOREIGN KEY REFERENCES 'task_' at line 1",

sql: "Create table if not exists user_task(UserId int FOREIGN KEY REFERENCES 'user_mgmt(id)', UserId int FOREIGN KEY REFERENCES 'task_mgmt(id)')"

I really don't know what is wrong there, hope you can help me with that.

user_5433763 · Answer

I would say you have there a typo since you're creating table with 2 columns with the same name and therefore I'm gonna write it in a form as I think it should be. The syntax for foreign keys for MYSQL should be at the end of table definition (just like other indexes, but might not be true for newer versions)

Create table if not exists user_task(
UserId int,
TaskId int,
FOREIGN KEY (UserId) REFERENCES user_mgmt(id),
FOREIGN KEY (TaskId) REFERENCES task_mgmt(id))

Create table with foreign key syntax error

1 Answers