user2923395
user2923395

Reputation: 327

Create table error - invalid identifier

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

Answers (1)

Vishwanath Dalvi
Vishwanath Dalvi

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

Related Questions