I have created tables like before, with the given primary and foreign keys. However I get this error when I try to create a new table with the code below.
create table Order (
oid int(255),
sid int(255),
sku int(255),
quantity int(255),
foreign key (sid) references Suppliers(sid),
foreign key (sku) references Parts(sku),
primary key(sid,sku)
)
and I have created Suppliers and Parts tables with the code below
create table Parts(
sku int(255) auto_increment primary key,
pname varchar(255),
stock_level int(255),
color varchar(255)
)
create table Suppliers (
sid int(255) auto_increment primary key,
sname varchar(255),
city varchar(255),
street varchar(255)
)
sid and sku already exist in their respective tables. I do not understand why I get such an error.
The complete output is:
[42000][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 ‘Order( oid int (255), sid int (255), sku int(255), quantity
i’ at line 1
2
Answers
I solved the problem with creating the table with the name "Orders" instead of just Order. I think it got confused with the order by keyword hence the syntax error.
I suppose it chokes on
INT(255)
.INT
is a signed four-byte integer. Its max positive value is 2147483647.So, just use
INT
and notINT(255)
, and it will work.Check this page on the topic, for example:
https://blog.devart.com/mysql-int-data-type.html#what_is_mysql_integer