Cannot add FOREIGN KEY constraint using ALTER TABLE

Question

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.

and in Contact id which column name you have? .add your schema .. + — ScaisEdge
– ScaisEdge, Commented Mar 18, 2018 at 14:53
Please show the ddl for your table(s) and describe the relationship you're trying to codify. — thatjeffsmith
– thatjeffsmith, Commented Mar 18, 2018 at 15:00

Gordon Linoff · Accepted Answer · 2018-03-18 14:52:10Z

2

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);

answered Mar 18, 2018 at 14:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hymcode Over a year ago

WOW I cannot believe I forgot to even add the column Customer_ID into the Contact table. Thank you very much this solved the issue.

Ronald Pereira · Accepted Answer · 2018-03-18 14:53:16Z

1

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);

answered Mar 18, 2018 at 14:53

Ronald Pereira

4071 gold badge4 silver badges12 bronze badges

1 Comment

hymcode Over a year ago

Thanks for the response. You're correct, I forgot to add a column name in the Contact table called Customer_ID

Collectives™ on Stack Overflow

Cannot add FOREIGN KEY constraint using ALTER TABLE

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related