MySQL error 1064 creating table

Question

Trying to create a table with two foreign keys and keep getting this 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 'idbed MEDIUMINT UNSIGNED NOT NULL,
idnumber MEDIUMINT UNSIGNED NOT NULL,' at line 2

The table is:

CREATE TABLE care(
idbed MEDIUMINT UNSIGNED NOT NULL,
idnumber MEDIUMINT UNSIGNED NOT NULL,
PRIMARY KEY(idbed, idnumber),
FOREIGN KEY(idbed) REFERENCES intensivecarebed(idbed)
FOREIGN KEY(idnumber) REFERENCES employee(idnumber));

the other two tables are:

CREATE TABLE Employee(
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
address VARCHAR(60) NOT NULL,
salary FLOAT UNSIGNED NOT NULL,
specialization VARCHAR(50) NOT NULL<
clinic_name VARCHAR(50) NOT NULL,
PRIMARY KEY(idnumber)
);

CREATE TABLE intensivecarebed(
idbed MEDIUMINT UNSIGNED NOT NULL,
clinic_name VARCHAR(50) NOT NULL,
tax_reg_number TINYINT(10) UNSIGNED NOT NULL,
PRIMARY KEY(idbed)
);

Anybody insights?

Answer 1

There are several errors in your code:

• You missed idnumber in Employee table.
• You missed coma between foreign keys.
• You had < instead of ,

You also have to create care table after creating other two tables because you are referring to these tables.

Try this:

CREATE TABLE Employee(
    idnumber MEDIUMINT UNSIGNED NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    address VARCHAR(60) NOT NULL,
    salary FLOAT UNSIGNED NOT NULL,
    specialization VARCHAR(50) NOT NULL,
    clinic_name VARCHAR(50) NOT NULL,
    PRIMARY KEY(idnumber)
);

CREATE TABLE intensivecarebed(
    idbed MEDIUMINT UNSIGNED NOT NULL,
    clinic_name VARCHAR(50) NOT NULL,
    tax_reg_number TINYINT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(idbed)
);

CREATE TABLE care(
    idbed MEDIUMINT UNSIGNED NOT NULL,
    idnumber MEDIUMINT UNSIGNED NOT NULL,
    PRIMARY KEY(idbed, idnumber),
    FOREIGN KEY(idbed) REFERENCES intensivecarebed(idbed),
    FOREIGN KEY(idnumber) REFERENCES employee(idnumber)
);