-2

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

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Dominos-roadster
  • 1
  • 3

2 Answers2

0

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.

Dominos-roadster
  • 1
  • 3
-1

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 not INT(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

marcothesane
  • 6,192
  • 1
  • 11
  • 21
  • 1
    https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html – GSerg Nov 19 '22 at 11:51
  • It's true that INT is a 4-byte data type, so it makes no sense to use INT(255). However, it's not a syntax error. Even if it were an error, the error message reported by the OP tells us exactly where the error was found, and it wasn't in the INT(255) data type. – Bill Karwin Nov 19 '22 at 17:51