11 Replies Latest reply on Aug 21, 2002 1:36 PM by Cornelius Buschka

    Foreign Key not null

    Gianluca Chini Newbie

      I have one problem with CMR.
      I have a relationship based on a foreign key.
      The foreign key is defined not null in the RDBMS (Oracle).
      Obviously I can't pass the entity in the ejbCreate method, but i do it in the ejbPostCreate.

      But in this case jboss throws a sqlException (can not insert null into CUSTOMER_FK).

      It 's important not to remover this constraint!!
      What shall I do???
      Thanks

        • 1. Re: Foreign Key not null
          Dain Sundstrom Master

          Currently JBossCMP requires that all database foreign key fields be nullable (not not-null).

          • 2. Re: Foreign Key not null
            Arthur Wang Newbie

            Hi,

            Does JBoss intend to add function like weblogic, insert-until-postcreate in the deployment decriptor to defer the insert until ejbPostCreate is called? This is required in some business scenerio.

            Thanks
            Arthur

            • 4. Re: Foreign Key not null
              Cornelius Buschka Newbie

              Possibly you could delay constraints checking via deferred constraints, which is supported since Oracle8i.

              It's done by creating the table with constraints marked as deferrable.

              Example:
              create table client ( client_id number(10), name varchar(200) not null unique, constraint client_pk primary key ( client_id ) );

              create table customer ( customer_id number(10), name varchar(200) not null unique, password varchar(200) not null, client number(10) not null, constraint customer_pk primary key ( customer_id ), constraint customer_client_fk foreign key ( client ) references client deferrable );

              I think, you must activate it with SET CONSTRAINT customer_client_fk DEFERRED which is a setting local to the current Oracle session. I dont know if JBoss supports executing statements on getting a JDBC connection.

              I have no real experiences with it. Could you tell if you gather some experiences with it.

              • 5. Re: Foreign Key not null
                Dain Sundstrom Master

                My Oracle DBA friends tell me that using deferred does very bad things to the database.

                • 6. Re: Foreign Key not null
                  Cornelius Buschka Newbie

                  What bad things?

                  If isolation level is READ COMMITTED no other transaction is affected. At commit time the database will have the same state with deferred constraints as without.

                  O.k., you must pay attentiton for your queries before you have committed. You cannt rely on NOT NULL columns containing no NULL values (this is the intention).

                  But, I'm Java developer, no Oracle dba, I could be totally wrong.

                  P.S. I wrote this late because I thought my watches inform me via mail on new replies.

                  • 7. Re: Foreign Key not null
                    Cornelius Buschka Newbie

                    What bad things?

                    If isolation level is READ COMMITTED no other transaction is affected. And I think at commit time the database will have the same state with deferred constraints as without.

                    O.k., you must pay attentiton for your queries before you have committed. You cannt rely on NOT NULL columns containing no NULL values.

                    But, I'm Java developer, no DBA, I could be totally wrong.

                    P.S. I wrote this late because I thought my watches inform me via mail if a new reply arrives.

                    • 8. Re: Foreign Key not null
                      Cornelius Buschka Newbie

                      What bad things?

                      If isolation level is READ COMMITTED no other transaction is affected. And I think at commit time the database will have the same state with deferred constraints as without.

                      O.k., you must pay attentiton for your queries before you have committed. You cannt rely on NOT NULL columns containing no NULL values.

                      But, I could be totally wrong.

                      P.S. I wrote this late because I thought my watches inform me via mail if a new reply arrives.

                      • 9. Re: Foreign Key not null
                        Cornelius Buschka Newbie

                        What bad things?

                        If isolation level is READ COMMITTED no other transaction is affected. And I think at commit time the database will have the same state with deferred constraints as without.

                        O.k., you must pay attentiton for your queries before you have committed. You cannt rely on NOT NULL columns containing no NULL values.

                        But, I could be totally wrong.

                        P.S. I wrote this late because I thought my watches inform me via mail if a new reply arrives.

                        • 10. Re: Foreign Key not null
                          Cornelius Buschka Newbie

                          What bad things?

                          If isolation level is READ COMMITTED no other transaction is affected. And I think at commit time the database will have the same state with deferred constraints as without.

                          O.k., you must pay attentiton for your queries before you have committed. You cannt rely on NOT NULL columns containing no NULL values.

                          But, I could be totally wrong.

                          P.S. I wrote this late because I thought my wa

                          • 11. Re: Foreign Key not null
                            Cornelius Buschka Newbie

                            Sorry, I think my browser has a cache problem :(