CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
hymcode
hymcode

Reputation: 91

Cannot add FOREIGN KEY constraint using ALTER TABLE

everybody, I am relatively new to SQL and I am currently testing my database tables using Oracle Live SQL. I have a table called Customer and a table called Contact. Within the Contact table, I am trying to add a FOREIGN KEY constraint of the Customer_ID column into my Contact table, but keep getting an ORA-00904: "CUSTOMER_ID": invalid identifier, error using the code below: 

ALTER TABLE Contact ADD FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)

Any help would be much appreciated.

Upvotes: 2

Views: 949

Answers (2)

Ronald Pereira
Ronald Pereira

Reputation: 407

So based on the comments on your question, you don't have a Customer_ID column on your Contact table. The definition of a foreign key is that you have the column you want to reference in both tables.

ALTER TABLE Contact ADD Customer_ID int;
ALTER TABLE Contact ADD FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Presumably, you don't have the column Customer_Id in contact. So try this:

ALTER TABLE Contact ADD Customer_Id number;  -- the type is a guess

ALTER TABLE Contact ADD FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID);

Upvotes: 2

Related Questions