2 Replies Latest reply: Feb 11, 2013 12:11 PM by 989503 RSS

    Null Primary Keys

    989503
      Hello everyone! I've created a database for a football managers system I'm creating.. I'm using APEX to create a front end for it and currently have most of my pages completed.. the major problem I am running into is with the creation of the tabular forms to insert data into each of my tables.. I'll use my clubs table as an example..

      I've followed the guide here http://docs.oracle.com/cd/E14373_01/appdev.32/e13363/frm_tabular.htm to create the tabular form for my clubs table. I'm using the clubId field as the primary key in the tabular form wizard as you would expect, but the problem I have is that when I click the 'Add Row' button, it adds a new row, but the primary key is showing as Null. This is resulting in me being unable to insert any new clubs (or insert anything with APEX), because APEX is giving the error "Cannot insert null into primary key".

      So the question I have is, is there any way of instructing my forms to generate a random number for my primary keys? I thought this would be automatic but its driving me mad at the minute, its one of the last things I need to sort out!

      Thanks for any help! :)
        • 1. Re: Null Primary Keys
          Howard (... in Training)
          Hi,

          I don't believe sequence numbers can be generated automagically just yet. But that's coming in Oracle 12C, I hear. There are ways to get random number in APEX. But, would you consider an alternative? Create your own sequence and populate the key field from the sequence with an INSERT trigger on the table? That's pretty straight-forward once you've done it.

          Regards,
          Howard
          CREATE SEQUENCE  <table-name>_SEQ START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE ORDER NOCACHE;
          GRANT  SELECT ON <table-name>_SEQ TO <some-user>_ROLE;
          create or replace trigger <table-name>_INSERT
          before insert  on <table-name>
          for each row 
          BEGIN
            select <table-name>_SEQ.nextval into :new.<primary-key-column-name> from dual;
          end <table-name>_INSERT;
          /
          SHOW ERRORS
          Edited by: Howard (... in Training) on Feb 11, 2013 11:12 AM
          • 2. Re: Null Primary Keys
            989503
            Thats fantastic your a star, thats exactly what I'm looking for!! I'll report back here in the next few days if it goes wrong, but I've used that method for 2 of my tables now and tried multiple inserts and its working perfectly.

            Thank you! :)