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

    Foreign Key not null

    gchini

      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
          dsundstrom

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

          • 2. Re: Foreign Key not null
            arthursdw

            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

            • 3. Re: Foreign Key not null
              dsundstrom

              Yes

              • 4. Re: Foreign Key not null
                cbuschka

                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
                  dsundstrom

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

                  • 6. Re: Foreign Key not null
                    cbuschka

                    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
                      cbuschka

                      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
                        cbuschka

                        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
                          cbuschka

                          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
                            cbuschka

                            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
                              cbuschka

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