'MySQL error #1054 - Unknown column in 'Field List'
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!
Solution 1:[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
Solution 2:[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.
Solution 3:[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.
Solution 4:[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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Salem |
Solution 2 | Han |
Solution 3 | Lealo |
Solution 4 | Si Tavner |