Reputation: 327
I'm trying to create a table in Oracle 11g express, but I receive an error "ORA-00904:invalid identifier"
CREATE TABLE Ingredients(
IngredientID INTEGER PRIMARY KEY NOT NULL,
IngredientName VARCHAR(255),
IngredientClassID SMALLINT NOT NULL,
MeasureAmountID SMALLINT NOT NULL,
CHECK (IngredientID>0),
CHECK (IngredientClassID>0),
FOREIGN KEY (IngredientClassID) REFERENCES Ingredient_Classes,
ON UPDATE NO ACTION,
ON DELETE CASCADE,
FOREIGN KEY (MeasureAmountID) REFERENCES Measurements,
ON UPDATE NO ACTION,
ON DELETE CASCADE
);
Upvotes: 0
Views: 330
Reputation: 36681
Did you miss TableName(ColumnName) in foreign key and also added additional commas. check proper syntax http://docs.oracle.com/cd/E17952_01/refman-5.5-en/create-table-foreign-keys.html
I've not tested the below script.
CREATE TABLE Ingredients(
IngredientID INTEGER PRIMARY KEY NOT NULL,
IngredientName VARCHAR(255),
IngredientClassID SMALLINT NOT NULL,
MeasureAmountID SMALLINT NOT NULL,
CHECK (IngredientID>0),
CHECK (IngredientClassID>0),
FOREIGN KEY (IngredientClassID) REFERENCES Ingredient_Classes(ColumnName)
ON UPDATE NO ACTION
ON DELETE CASCADE,
FOREIGN KEY (MeasureAmountID) REFERENCES Measurements(ColumnName)
ON UPDATE NO ACTION
ON DELETE CASCADE
);
Upvotes: 1