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?

Ther is some syntax error in the following line: specialization VARCHAR(50) NOT NULL< < should be replaced by ',' in the first create After correcting this syntax error, i executed this sccript in my MySQL server it went ok... — ASNAOUI Ayoub
– ASNAOUI Ayoub, Commented Jan 21, 2015 at 13:09
specialization VARCHAR(50) NOT NULL< one < which must be ,. Don't you need idnumber MEDIUMINT UNSIGNED NOT NULL, also in table Employee? — Jason Paddle
– Jason Paddle, Commented Jan 21, 2015 at 13:10

rtruszk · Accepted Answer · 2015-01-21 13:22:07Z

2

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)
);

edited Jan 21, 2015 at 13:22

answered Jan 21, 2015 at 13:10

rtruszk

3,92213 gold badges40 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mm-Art-In Over a year ago

could you answer with a small explanation?

Collectives™ on Stack Overflow

MySQL error 1064 creating table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related