Trying to create a table with a foreign key. I keep getting ORA-00904 error. What am I doing wrong. Is it because table of the foreign key is not yet created ?
CREATE TABLE ingredients(
ingredient_id number(2,0),
ingredient VARCHAR2(55) NOT NULL,
quantity_required VARCHAR2(15) NOT NULL,
optional_ingredient VARCHAR2(30) NOT NULL,
CONSTRAINT pk_ingr_id PRIMARY KEY(ingredient_id),
CONSTRAINT fk_ingredient_list FOREIGN KEY(id) REFERENCES ingredient_list(id)
);
Take a look at the following line:
CONSTRAINT fk_ingredient_list FOREIGN KEY(id) REFERENCES ingredient_list(id)
Your table has no column named "id". I assume you meant to write
CONSTRAINT fk_ingredient_list FOREIGN KEY(ingredient_id) REFERENCES ingredient_list(id)
EDIT:
Additionally, as you suspected yourself, if you want to reference the ingredient_list table, you must create it before creating the ingredients table that references it.
We never know but in my case I had this exception no matter what name I gave my column. I had a library with EF which pointed on the proper database. The entities were correct.
But on the client part (a web application), the connection string pointed to another database! A stupid mistake, it was hard to pinpoint this silly misgave....
So please have a look at the connection string FIRST :)
