Mysql statement is having syntax issue

Question

I am running below the MYSQL query and getting this error. Can someone help me here? I am using xampp to run MySQL and executing the statement

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL, DeliveryMode varchar NOT NULL, Qty int NOT NULL, Spicy ' at line 6

Below is my create table query

CREATE TABLE Bag (
    BagId int NOT NULL AUTO_INCREMENT,
    DishName varchar NOT NULL,
    DeliveryMode varchar NOT NULL,
    Qty int NOT NULL DEFAULT 1,
    Spicy varchar(20) NULL,
    SpecialInstructionsFood varchar(250),
    BagStatus varchar(100) NOT NULL,
    DateCreated datetime DEFAULT current_timestamp(),
    DateUpdated datetime DEFAULT current_timestamp(),
    FOREIGN KEY(DishName) REFERENCES Dish(DishName),
    CONSTRAINT FK_DishName_Bag FOREIGN KEY(DishName)
    REFERENCES Dish(DishName),
    FOREIGN KEY(DeliveryMode) REFERENCES DeliveryModeType(Name),
    CONSTRAINT FK_DeliveryModeType_Bag FOREIGN KEY(DeliveryMode)
    REFERENCES DeliveryModeType(Name),
    FOREIGN KEY(Spicy) REFERENCES SpicyType(Name),
    CONSTRAINT FK_SpicyType_Bag FOREIGN KEY(Spicy)
    REFERENCES SpicyType(Name),
    FOREIGN KEY(BagStatus) REFERENCES BagStatusType(Name),
    CONSTRAINT FK_BagStatusType_Bag FOREIGN KEY(BagStatus)
    REFERENCES BagStatusType(Name),
    PRIMARY KEY (BagId)
);

'CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store.' - dev.mysql.com/doc/refman/8.0/en/char.html , you need to provide length for dishname and deliverymode if still failing add your FK reference tables. — P.Salmon
– P.Salmon, Commented Sep 30, 2020 at 7:56

Dark Knight · Accepted Answer · 2020-09-30 03:33:40Z

1

When you define a varchar in mysql you must provide the max length that it supports.

So modify your columns(DishName & DeliveryMode) which uses varchar as datatype to something like varchar(50) whichever suits them.

DishName VARCHAR(50) NOT NULL, 
DeliveryMode VARCHAR(20) NOT NULL

answered Sep 30, 2020 at 3:33

Dark Knight

6,6211 gold badge22 silver badges40 bronze badges

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

4 Comments

Asif Ali Khan Over a year ago

I dont think it allowed me, because its a foreign key define in the sql statement

Dark Knight Over a year ago

Use the same data type with same length that is defined in the foreign table for both of the fields. If you're using their primary_key(if integer) then change the datatype in same manner.

FanoFN Over a year ago

"I dont think it allowed me ..." means you haven't test it yet @AsifAliKhan ?

Asif Ali Khan Over a year ago

Your solution didnt worked @JitendraYadav . But i got the solution now, Actually I was trying to refer to a table in which the primary key was not declared.

Ankit Jindal · Accepted Answer · 2020-09-30 03:46:35Z

0

CREATE TABLE Bag (
    BagId int(11) NOT NULL AUTO_INCREMENT,
    DishName varchar(100) NOT NULL,
    DeliveryMode varchar(40) NOT NULL,
    Qty int(11) NOT NULL DEFAULT 1,
    Spicy varchar(20),
    SpecialInstructionsFood varchar(250),
    BagStatus varchar(100) NOT NULL,
    DateCreated datetime DEFAULT current_timestamp(),
    DateUpdated datetime DEFAULT current_timestamp(),
    FOREIGN KEY(DishName) REFERENCES Dish(DishName),
    CONSTRAINT FK_DishName_Bag FOREIGN KEY(DishName)
    REFERENCES Dish(DishName),
    FOREIGN KEY(DeliveryMode) REFERENCES DeliveryModeType(Name),
    CONSTRAINT FK_DeliveryModeType_Bag FOREIGN KEY(DeliveryMode)
    REFERENCES DeliveryModeType(Name),
    FOREIGN KEY(Spicy) REFERENCES SpicyType(Name),
    CONSTRAINT FK_SpicyType_Bag FOREIGN KEY(Spicy)
    REFERENCES SpicyType(Name),
    FOREIGN KEY(BagStatus) REFERENCES BagStatusType(Name),
    CONSTRAINT FK_BagStatusType_Bag FOREIGN KEY(BagStatus)
    REFERENCES BagStatusType(Name),
    PRIMARY KEY (BagId)
);

answered Sep 30, 2020 at 3:46

Ankit Jindal

4,0963 gold badges32 silver badges41 bronze badges

2 Comments

Dark Knight Over a year ago

does this answer provides some more info than the existing one? If so, it would be great if you can a little explanation for that.

Asif Ali Khan Over a year ago

Hi Ankit, I dont think adding max length will fix it, because those variables are foreign key that is defined below in the mysql syntax. I think there is some other issue.

Collectives™ on Stack Overflow

Mysql statement is having syntax issue

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related