Search code examples
plsqlforeign-keysalter-tablealter

invalid alter table option while modify foreign key

I want to modify the update option of one foreign key. For this I executed this command:

 alter table testusers.ORDERS
 DROP CONSTRAINT ORDER_FK_2,
 ADD CONSTRAINT ORDER_FK_2 FOREIGN KEY(FK_PRODUCER_ID) REFERENCES testuser.PRODUCER      (producer_id) 
 ON UPDATE CASCADE ON DELETE CASCADE;

If I execute this, there is the following error:

 SQL-Fehler: ORA-01735: Ungültige Option ALTER TABLE
 01735. 00000 -  "invalid ALTER TABLE option"
Solution

  • There is no comma separated list for the alter table according to documentation syntax diagram http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm#CJAEDFIB

    create table orders(order_id number, fk_producer_id number, CONSTRAINT order_pk PRIMARY KEY (order_id));

create table producer(producer_id number, CONSTRAINT producer_pk PRIMARY KEY (producer_id));

alter table orders
  ADD CONSTRAINT ORDER_FK_2 FOREIGN KEY( FK_PRODUCER_ID) 
  REFERENCES PRODUCER      (producer_id) ;

alter table orders 
DROP CONSTRAINT ORDER_FK_2;

alter table orders
  ADD CONSTRAINT ORDER_FK_2 FOREIGN KEY( FK_PRODUCER_ID) 
  REFERENCES PRODUCER      (producer_id) ;

    Ahm, yes, and I could not find any ON UPDATE CASCADE syntax either. But I am sure you can work it out now. Otherwise drop a little comment or post a new question.