CODEWITHSUNDEEP
sqloracle-databaseforeign-keysalter
Stumbler
Stumbler

Reputation: 2146

Altering Oracle table: invalid identifier

Using Oracle Application Express for Oracle 11g

Adding a foreign key constraint to a preexisting table.

Table: COMMUNICATION

COMMUNICATION-ID      NUMBER        
COMMUNICATIONTYPE_ID  VARCHAR2(6)   
CONTACT_ID            NUMBER         
COMMUNICATIONVALUE    VARCHAR2(40)

Table: COMMUNICATIONTYPE

COMMUNICATIONTYPE-ID VARCHAR2(6)

Using the following SQL in the SQL Command Interface

ALTER TABLE COMMUNICATION
ADD CONSTRAINT FK_COMMUNICATIONTYPE
FOREIGN KEY (COMMUNICATIONTYPE_ID)
REFERENCES COMMUNICATIONTYPE(COMMUNICATIONTYPE-ID)

Returns the following:

ORA-00904: : invalid identifier

Didn't think this could be any simpler. What on earth could be going wrong?

Upvotes: 0

Views: 3837

Answers (2)

APC
APC

Reputation: 146239

COMMUNICATIONTYPE(COMMUNICATIONTYPE-ID)

Dash is not valid in Oracle names. I think you meant to type an underscore. It's easily done.

As the DDL for the COMMUNICATION table the same typo that create table script may have failed as well. So the error could have been thrown in more than one place.

Upvotes: 1

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

Are you sure the field is called COMMUNICATIONTYPE-ID?

- is not normally used in identifiers as it has special meaning.

If I were you I would rename the column COMMUNICATIONTYPE_ID

To use it in an identifier you need to always surround that identifier in double quotes.

ALTER TABLE COMMUNICATION
ADD CONSTRAINT FK_COMMUNICATIONTYPE
FOREIGN KEY (COMMUNICATIONTYPE_ID)
REFERENCES COMMUNICATIONTYPE("COMMUNICATIONTYPE-ID")

Upvotes: 1

Related Questions