Error: Column doesn't exist in table (Foreign Key referencing)

Question

Question
I am trying to link the ID column from the Customer's table to C_ID in the Purchases table. I am still learning SQL so I have a light knowledge and am not sure why this error is occuring. If someone could offer up a solution and point where I've went wrong that would be great.

Error Message

SQL Code

CREATE TABLE Customer (

ID INTEGER,
Firstname VARCHAR (15),
Lastname VARCHAR (15),
Address VARCHAR (254),
Postcode VARCHAR (8),
Email VARCHAR (254),
Phoneno INTEGER,
Points INTEGER,
PRIMARY KEY (ID)
);

CREATE TABLE Purchases (

C_ID INTEGER,
GameName VARCHAR(30),
ConsoleType VARCHAR (20),
Price VARCHAR (254),
PaymentType VARCHAR (20),
Date TIMESTAMP,
PointsGained INTEGER,
PRIMARY KEY (C_ID),
FOREIGN KEY (ID) REFERENCES Customer(ID)
);

Error messages are sometimes very specific. This is one of those occasions. If you want to be a programmer, reading and interpreting error messages are skills you need. This one is so clear and specific that it suggest that you might not have read it at all. Having said that, sometimes the messages are vague, but that's a different story. — Dan Bracuk, Mar 29 '16 at 16:20

score 2 · Answer 1 · answered Mar 29 '16 at 15:28

2

I think you misplaced the primary key and foreign key column also you didn't add Purchase table primary key column

CREATE TABLE Purchases 
(
ID INTEGER, -- Primary key column 
C_ID INTEGER,
GameName VARCHAR(30),
ConsoleType VARCHAR (20),
Price VARCHAR (254),
PaymentType VARCHAR (20),
Date TIMESTAMP,
PointsGained INTEGER,
PRIMARY KEY (ID),
FOREIGN KEY (C_ID) -- Replace ID with C_ID
    REFERENCES Customer(ID)  
);

answered Mar 29 '16 at 15:28

Pரதீப்

91,748
19
131
172

1

beat me by a second :-) – Zohar Peled Mar 29 '16 at 15:30
@ZoharPeled - Ha ha that too both of us came up with similar thoughts :P – Pரதீப் Mar 29 '16 at 15:31

score 1 · Answer 2 · answered Mar 29 '16 at 15:28

You don't have a column named ID in your Purchases table.
Seems to me it should be like this:

CREATE TABLE Purchases (
    ID INTEGER,
    C_ID INTEGER,
    GameName VARCHAR(30),
    ConsoleType VARCHAR (20),
    Price VARCHAR (254),
    PaymentType VARCHAR (20),
    Date TIMESTAMP,
    PointsGained INTEGER,
    PRIMARY KEY (ID),
    FOREIGN KEY (C_ID) REFERENCES Customer(ID)
);

So that the ID column is the primary key and the C_ID column is the foreign key to customers.

Error: Column doesn't exist in table (Foreign Key referencing)

2 Answers2

Linked