Deferrable Initially Deferred problem

Discussions

EJB programming & troubleshooting: Deferrable Initially Deferred problem

  1. Deferrable Initially Deferred problem (2 messages)

    (Referencing a discussion previously posted on this forum I have tried a suggested solution)
      I have a problem in Oracle 9i and it is like this: I have two tables A and B and table's A primary key is a foreign key in table B. This cannot be null so I have added a constraint like:
    ALTER TABLE B ADD CONSTRAINT FK_A
     FOREIGN KEY (A_ID)
      REFERENCES A (A_ID) DEFERRABLE INITIALLY DEFERRED;
    As far as I understood on this forum this should set the constraint to be verified only at the and of a transaction and thus the update of the foreign key(in postEjbCreateB) would be executed before the insertion.

    My problem is that I get the same exception at insertion saying that the FK_A cannot be null.

    I have seen in the database that the script for table B is like:
    ALTER TABLE B ADD CONSTRAINT FK_A
     FOREIGN KEY (A_ID)
      REFERENCES A (A_ID) DEFERRABLE; <--lack of INITIALLY DEFERRED
    I cannot see the problem here. I have used SQL Plus to add the constraint. Does anyone see any problem?

    Thanks, Sergiu.

    Threaded Messages (2)

  2. deferrable[ Go to top ]

    i kinda solved your problem! what's going wrong with your script is that at least in oracle 8i those kind of references deferrable should be define when you create the table not in an alter table statement. here's one example

    CREATE TABLE CER_CER_CERTIFICADO (
      CER_ID NUMBER NOT NULL,
      CER_FECHA_INICIO DATE NOT NULL,
      AER_COD VARCHAR2 (20),
      TCE_COD VARCHAR2 (20) CONSTRAINT FK_CER_TCE
           REFERENCES CER_TCE_TIPOCERTIFICADO(TCE_COD) NOT NULL DEFERRABLE
           INITIALLY DEFERRED,
      CER_FECHA_FIN DATE NOT NULL,
      AUT_ID NUMBER,
      PAI_COD VARCHAR2 (2),
      CER_NUMERO VARCHAR2 (20) NOT NULL,
      ESD_ESTADO VARCHAR2 (3),
      PRIMARY KEY ( CER_ID )
      );

    then TCE_COD references another table and would be check until commit, but i guess this is not the best solution. if anyone knows how it should be let me know

    Luis Ramz.
  3. deferrable[ Go to top ]

    Thanks Luis, I'll try your solution ;)).