Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Unanswered: stored proc. syntax

    I am porting from DB2, could someone please tell me why these do not work? Many thanks.

    ALTER TABLE STPUSER.AKIS_SNAPSHOT_DATA2
    ADD CONSTRAINT AKCT_RULE_ID FOREIGN KEY (RULE_ID,"VERSION")
    REFERENCES STPUSER.AKIS_AUDIT (RULE_ID,"VERSION") ON DELETE NO ACTION ON UPDATE NO ACTION
    ;


    ALTER TABLE STPUSER.AKIS_ADVISE_INSTANCE
    ADD CONSTRAINT AKCTS003
    PRIMARY KEY (START_TIME)
    ;


    ALTER TABLE STPUSER.AKIS_ADVISE_INDEX
    ADD CONSTRAINT AKCTR001 FOREIGN KEY (RUN_ID) REFERENCES STPUSER.AKIS_ADVISE_INSTANCE (START_TIME) ON DELETE CASCADE ON UPDATE NO ACTION
    ;



    ALTER TABLE STPUSER.AKIS_ADVISE_MQT
    ADD CONSTRAINT AKCT0003 FOREIGN KEY (RUN_ID) REFERENCES STPUSER.AKIS_ADVISE_INSTANCE (START_TIME) ON DELETE CASCADE ON UPDATE NO ACTION
    ;


    ALTER TABLE STPUSER.AKIS_ADVISE_PARTITION
    ADD CONSTRAINT AKCT7290 FOREIGN KEY (RUN_ID) REFERENCES STPUSER.AKIS_ADVISE_INSTANCE (START_TIME) ON DELETE CASCADE ON UPDATE NO ACTION
    ;


    ALTER TABLE STPUSER.AKIS_SNAPSHOT_DATA2
    ADD CONSTRAINT AKCT0308175643180
    PRIMARY KEY (SNAPSHOT_ID,RULE_ID,"VERSION")
    ;

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    There is no "update cascade" in an Oracle constraint. You have to code that (like, in a trigger). So, remove that completely from your constraint definitions.

    I've never seen the "on delete no action" syntax before, so I'm not going to say it won't work ... but if you omit it, you get the same effect.

    Code:
    ALTER TABLE STPUSER.AKIS_SNAPSHOT_DATA2 
    ADD CONSTRAINT AKCT_RULE_ID FOREIGN KEY (RULE_ID,"VERSION") 
    REFERENCES STPUSER.AKIS_AUDIT (RULE_ID,"VERSION");

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •