-1

I'm getting a mysql create table syntax error and hoping someone can spot it before I loose my sanity.

create table buyer_representations (
Key int not null auto_increment,
Client_ID int not null,
Start_Time varchar(5),
Start_Date date,
End_Date date,
Property_Type varchar(255),
Geographic_Location varchar(255),
Commission varchar(255),
Alternate_Commission varchar(255),
Lease_Commission varchar(255),
Holdover varchar(255),
Clauses varchar(2000),
primary key (Key)
);

ERROR 1064 (42000): 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 'int not null auto_increment, Client_ID int not null, Start_Time varchar(5), Star' at line 2

mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper

I tried adding 'primary key' at the end of line 2, as well a default value, and switching up 'not null' with 'auto_increment' .. but nothing is working.

Edit: I also tried changing Key to KeyId, and putting Key in quotes .. but the error remains the same.

Final Edit: putting Key inside backquotes finally worked

create table buyer_representations (
`Key` int not null auto_increment,
Client_ID int not null,
Start_Time varchar(5),
Start_Date date,
End_Date date,
Property_Type varchar(255),
Geographic_Location varchar(255),
Commission varchar(255),
Alternate_Commission varchar(255),
Lease_Commission varchar(255),
Holdover varchar(255),
Clauses varchar(2000),
primary key (`Key`)
);
ak1percent
  • 1
  • 2
  • 1
    `Key` is a MySQL keyword and can't (or shouldn't) be used for table/column names. Use a different name. – Progman Mar 27 '21 at 14:35
  • Please [edit] your question to include the new query you are executing and the complete error message you get now with the changed query. – Progman Mar 27 '21 at 14:55
  • Both added queries with `Key` in quotes and the one using the name `KeyId` instead are working perfectly. Looks like you are still running the old query when you still get the same error message. – Progman Mar 27 '21 at 15:14

2 Answers2

3

Key is a reserve keyword, rename that and you should be golden.

A complete list of keywords and reserved words can be found in section 10.3 Keywords and Reserved Words. Below

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

in your case, this should work (I name it test, but rename it to whatever fits your situation)

create table buyer_representations (
test int not null auto_increment,
Client_ID int not null,
Start_Time varchar(5),
Start_Date date,
End_Date date,
Property_Type varchar(255),
Geographic_Location varchar(255),
Commission varchar(255),
Alternate_Commission varchar(255),
Lease_Commission varchar(255),
Holdover varchar(255),
Clauses varchar(2000),
primary key (test)
);
chewie
  • 529
  • 4
  • 17
1

The problem is the column name you have used in your table. The first column name is Key. Key is considered as a reserved word in Mysql and it has a special meaning. That is why there is an error.

If you want to use the same column name you can do it by writing the column name inside ``.

For eg. create table buyer_representations ( `Key` int not null auto_increment,

This will work fine.

IMPERIO
  • 62
  • 3
  • Thanks for the tip .. I tried adding the quotes, but still get the same error. – ak1percent Mar 27 '21 at 14:54
  • You may be using ' ' these as the quotes. Rather you have to use ` ` as the quotes. This symbol ` is present on the left side of the number 1 in the qwerty keyboard. – IMPERIO Mar 27 '21 at 15:04
  • Also apply this in the primary key section as you have Key as the primary key for your table. It should work – IMPERIO Mar 27 '21 at 15:07