Re: Nested Sequence

Hi,

     From what you have written it looks like you are using SEQUECNE number to  identify employers/contacts to the end user. This should NOT be done. A  single sequence is required for use in the primary key for each of your tables  e.g. EMP_SEQ for Employers table and CON_SEQ for contacts table. Use  CG_CODE_CONTROLS to record the actuale employers number. This will provide a  consecutive sequence of numbers, which SEQUECES WILL NOT. Next use a  database trigger to calculate the next contact number when the new record in  inserted
e.g.

     Employer table
          ID     NOT NULL NUMBER;
          EMP_NO  NOT NULL  NUMBER(5,0);
 
           Primary Key = ID
          Unique Key = EMP_NO

     Contacts table
           ID  NOT NULL NUMBER;
            EMP_ID  NOT NULL NUMBER;
           CONTACT_NO NOT NULL NUMBER;


          Primary Key = ID
          Unique Key = EMP_ID,CONTACT_NO
          EMP_ID References ID IN Employer
       

         you now new an ON-INSERT database trigger for Contacts
        parameters = EMP_ID
         select max(contact_no)+1 into contact_no from contacts 
         where emp_id = :emp_id

Advantages
     There will be no gaps in employer numbers
     Contacts can be moved to different companies.
     If contact leaves the others can be shuffeled to remove any gap in the