Search code examples
sqloracle-databaseforeign-keysalter

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?

Solution

  • 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")