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!
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