MySQL error #1054 - Unknown column in 'Field List'

Question

Whenever I try to input data into my tblorder I get the error message #1054 - Unknown column 'FK_Customer_ID' in 'field list'. I have tried breaking my code down and in doing this I found that the error is repeated for FK_Customer_ID and OrderQuantity whereas FK_DVD_ID it will take single data entries. I have tried dropping the table and recreating it, I have dropped the database and recreated it but nothing works. As far as I can tell my code is correct along with my spelling so I'm really stuck.

My tblorder is-

CREATE TABLE tblorder
(   
 Order_ID INT AUTO_INCREMENT NOT NULL,  
 FK_Customer_ID INT NOT NULL,   
 FK_DVD_ID INT NOT NULL,    
 OrderDate DATETIME NOT NULL DEFAULT NOW(),
 OrderQantity INT NOT NULL, 
 PRIMARY KEY (Order_ID),    
 FOREIGN KEY (FK_Customer_ID) REFERENCES tblcustomer (Customer_ID), 
 FOREIGN KEY (FK_DVD_ID) REFERENCES tbldvd (PK_ID)
);

The data I am trying to put in is-

INSERT INTO tblorder
 (FK_Customer_ID, FK_DVD_ID, OrderQuantity)
VALUES 
 (1, 3, 2),
 (1, 5, 1),
 (1, 10, 4), 
 (1, 15, 3),
 (2, 5, 4),
 (2, 17, 3),
 (3, 15, 1),
 (3, 16, 1),
 (3, 17, 1);

FK_Customer_ID is addressing -

CREATE TABLE tblcustomer
(
 Customer_ID INT AUTO_INCREMENT NOT NULL,
 FirstName VARCHAR(50) NOT NULL,
 LastName VARCHAR(50) NOT NULL,
 Age INT NOT NULL,
 PRIMARY KEY (Customer_ID)
);

FK_DVD_ID is addressing -

CREATE TABLE tblDVD
(
 PK_ID INT AUTO_INCREMENT NOT NULL,
 Title VARCHAR(100) NOT NULL,
 DIrector VARCHAR(100) NOT NULL,
 Genre VARCHAR(40) NOT NULL,
 dvd_Year YEAR NOT NULL,
 Price FLOAT(2) NOT NULL,
 Quantity INT NOT NULL,
 PRIMARY KEY (PK_ID)
);

Any help in fixing the will be greatly appreciated as it will help me with my A2 computing lesson!

Samphire

Use phpmyadmin to create your tables including the foreign key contraints and compare wigh what you got. – Electrometallurgy

Answer 1

You have an error in your OrderQuantity column. It is named "OrderQuantity" in the INSERT statement and "OrderQantity" in the table definition.

Also, I don't think you can use NOW() as default value in OrderDate. Try to use the following:

 OrderDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Example Fiddle

Schall

Answer 2

This, for me, was one of those incredibly stupid errors (on my part), but it took a lot of time to hunt it down. I created a new table, and got the #1054 error repeatedly, even though the table did have the 'unknown column'.

Then as I was gazing at the table definition wondering what the problem was, I noticed that the column names, almost imperceptibly, didn't perfectly line up vertically on the columns list.

Turns out there was a SPACE that snuck into the column name definition. DOH! So instead of 'colName' the actual column name was ' colName'. Hence the error msg.

I know this is dumb, but sometimes errors are like that. Just thought I'd mention it, in case this problem is lurking there for someone else.

Carew

Answer 3

I had this error aswell.

I am working in mysql workbench. When giving the values they have to be inside "". That solved it for me.

Evannia

Answer 4

I've just encountered the same problem, but for me the fix was different; I had a mismatched between the column name in the table I was updating and the column name in a history table, which is updated by a trigger. Correcting this mismatch fixed the problem immediately.

Tabatha