14 Replies Latest reply: Feb 23, 2013 7:32 AM by Christine Schnittker RSS

    How to generate composite primary key by trigger without using sequences

    Zafar Iqbal
      Hello,

      I have two column in my table colA and ColB, these two columns are the composite primary key. I want to generate a value for the colB by trigger. It will reset on colA.
      Please suggest me a solution how can I do it.

      Regards,
        • 1. Re: How to generate composite primary key by trigger without using sequences
          BluShadow
          Your requirement will not work in a multi-user environment.
          the moment you talk about "resetting" a key value, you are going to have to take out an exclusive lock on the whole table for the transaction to prevent other users from inserting and interfering.

          What is the business requirement you are trying to solve?
          Please post some example data and expected results as described in the FAQ: {message:id=9360002}
          • 2. Re: How to generate composite primary key by trigger without using sequences
            damorgan
            No version number and you want someone to suggest to you a way to not use "best practices."

            Why? Please provide a justification or no help is possible.
            • 3. Re: How to generate composite primary key by trigger without using sequences
              Zafar Iqbal
              Database Version:
              Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
              PL/SQL Release 10.2.0.1.0 - Production
              CORE     10.2.0.1.0     Production
              TNS for Linux: Version 10.2.0.1.0 - Production
              NLSRTL Version 10.2.0.1.0 - Production
              create table x(
              company_id                          not null varchar2(3),
              location_id                         not null varchar2(3),
              account_code                                 varchar2(10),
              created_by                                   varchar2(50),
              time_stamp                                   date,
              last_updated_by                              varchar2(50),
              last_updated_on                              date
              );
              Sample Data
              insert into x
                 (company_id, location_id, account_code, created_by, time_stamp, last_updated_by, last_updated_on)
               values
                 ('001', '001', '0101100001', '0010011', to_date('02/22/2013 11:49:11', 'MM/DD/YYYY HH24:MI:SS'), 
                  '0010011', to_date('02/22/2013 11:49:11', 'MM/DD/YYYY HH24:MI:SS'));
              insert into x
                 (company_id, location_id, account_code, created_by, time_stamp, last_updated_by, last_updated_on)
               values
                 ('001', '002', '0101100001', '0010011', to_date('02/22/2013 11:49:31', 'MM/DD/YYYY HH24:MI:SS'), 
                  '0010011', to_date('02/22/2013 11:49:31', 'MM/DD/YYYY HH24:MI:SS'));
              insert into x
                 (company_id, location_id, account_code, created_by, time_stamp, last_updated_by, last_updated_on)
               values
                 ('001', '003', '0101100001', '0010011', to_date('02/22/2013 11:50:03', 'MM/DD/YYYY HH24:MI:SS'), 
                  '0010011', to_date('02/22/2013 11:50:03', 'MM/DD/YYYY HH24:MI:SS'));
              compan_id and location_id are composit primary key, if company_id is 001 then location_id will be 001, 002, 003 .... and so on, if company_id is 002 then locations_id will again start from 001, 002, 003... and so on,
              company_id is entered by the user but location_id will be auto generated.
              • 4. Re: How to generate composite primary key by trigger without using sequences
                Paul  Horth
                Why reset?

                Why do your location ids have to be starting at 1? Surely they're just meaningless numbers?

                Just keep using the sequence without resetting.
                • 5. Re: How to generate composite primary key by trigger without using sequences
                  Zafar Iqbal
                  Dear Paul Horth,

                  I respect your reply but that is not a solution :)

                  In my opinion, I have this solution
                  create or replace trigger generate_x_loc_id before insert on x referencing new as new old as old for each row
                  begin
                      declare
                          v_loc_id varchar2(3);
                      begin
                          select lpad(nvl(max(to_number(location_id)), 0) + 1, 3, '0')
                            into v_loc_id
                            from x
                           where company_id = :new.company_id;
                      
                          :new.location_id := v_loc_id;
                  
                      end;
                  end;
                  • 6. Re: How to generate composite primary key by trigger without using sequences
                    _Karthick_
                    Zafar Iqbal wrote:
                    Dear Paul Horth,

                    I respect your reply but that is not a solution :)

                    In my opinion, I have this solution
                    create or replace trigger generate_x_loc_id before insert on x referencing new as new old as old for each row
                    begin
                    declare
                    v_loc_id varchar2(3);
                    begin
                    select lpad(nvl(max(to_number(location_id)), 0) + 1, 3, '0')
                    into v_loc_id
                    from x
                    where company_id = :new.company_id;
                    
                    :new.location_id := v_loc_id;
                    
                    end;
                    end;
                    Nice solution (Head banging on the desk). But its just that you totally forgot about concurrent users inserting into the same table.
                    • 7. Re: How to generate composite primary key by trigger without using sequences
                      Paul  Horth
                      Zafar Iqbal wrote:
                      Dear Paul Horth,

                      I respect your reply but that is not a solution :)
                      Yes it is a solution. It is the best kind of solution - one that makes the problem irrelevant (very Zen!).

                      Explain to me why it isn't a solution. Are you saying that something relies on these location ids starting at 1? Does it also rely
                      on them being contiguous?
                      In my opinion, I have this solution
                      create or replace trigger generate_x_loc_id before insert on x referencing new as new old as old for each row
                      begin
                      declare
                      v_loc_id varchar2(3);
                      begin
                      select lpad(nvl(max(to_number(location_id)), 0) + 1, 3, '0')
                      into v_loc_id
                      from x
                      where company_id = :new.company_id;
                      
                      :new.location_id := v_loc_id;
                      
                      end;
                      end;
                      BTW, your solution won't work. You seem to forget that Oracle is a multi-user system.

                      Edited by: Paul Horth on Feb 22, 2013 10:17 AM

                      Edited by: Paul Horth on Feb 22, 2013 10:38 AM
                      • 8. Re: How to generate composite primary key by trigger without using sequences
                        Stew Ashton
                        I am not a fan of triggers, but here is an attempt to manage a multi-user situation. Basically, what you need to do is serialize on company_id and store the highest value of location_id somewhere else so you can maintain it.
                        CREATE TABLE COMPANY(
                          COMPANY_ID VARCHAR2(3) PRIMARY KEY,
                          LOCATION_CODE NUMBER DEFAULT 0 not null
                        );
                        INSERT INTO COMPANY
                        SELECT COMPANY_ID, TO_number(MAX(LOCATION_ID)) FROM X
                        group by company_id;
                        
                        ALTER TABLE X ADD CONSTRAINT FK_X FOREIGN KEY (COMPANY_ID) REFERENCES COMPANY;
                        
                        create or replace trigger generate_x_loc_id before insert on x for each row
                        DECLARE
                          V_LOC_ID X.LOCATION_ID%TYPE;
                          v_company_id x.company_id%type := :NEW.COMPANY_ID;
                        BEGIN
                          UPDATE COMPANY SET LOCATION_CODE = LOCATION_CODE+1
                          WHERE COMPANY_ID = V_COMPANY_ID
                          RETURNING TO_CHAR(LOCATION_CODE, 'fm000') INTO V_LOC_ID;
                          
                          :NEW.LOCATION_ID := V_LOC_ID;
                        END GENERATE_X_LOC_ID;
                        /
                        Now try this code from two different sessions. The second session will wait for the first session to commit.
                        SELECT * FROM X;
                        INSERT INTO X SELECT * FROM X WHERE ROWNUM = 1;
                        SELECT * FROM company;
                        SELECT * FROM X;
                        If you insert records for a second company, the two sessions can insert locations concurrently as long as the companies are different.

                        This does not solve the problem of gaps caused by deletes, and it does not prevent updates to the location_id.
                        • 9. Re: How to generate composite primary key by trigger without using sequences
                          Paul  Horth
                          Yes, but I still ask what is the necessity of having location ids that start at 1 for each company id.

                          Being sequence generated, aren't they just surrogate keys: they have no business meaning.

                          So why not
                          company_id    location_id
                          1                   1
                          1                   2
                          2                   3
                          1                   4
                          3                   5
                          2                   6
                          • 10. Re: How to generate composite primary key by trigger without using sequences
                            Gaff
                            Paul and the others are correct. This is probably just another case of someone wanting sequences to be something they are not. But if you MUST have the location ID start at 1 for every company then you could probably have a sequence for each company and use the appropriate one. It requires you to know your list of possible companies but that shouldn't be a big deal I would think. Either way, it looks like you should use a sequence and not worry about starting at 1 or gaps etc. but you seem to be stuck on that. Try multiple sequencing and don't "reset" anything.

                            So ironically your solution might be to use multiple sequences instead of no sequences :)
                            • 11. Re: How to generate composite primary key by trigger without using sequences
                              Arash
                              add your solution:

                              create table dummy (id number(5) primary key);
                              insert into dummy values(1);
                              commit;

                              and change your trigger to:

                              create or replace trigger generate_x_loc_id before insert on x referencing new as new old as old for each row
                              begin
                              declare
                              v_loc_id varchar2(3);
                              begin
                              update dummy
                              set id = 1;
                              select lpad(nvl(max(to_number(location_id)), 0) + 1, 3, '0')
                              into v_loc_id
                              from x
                              where company_id = :new.company_id;
                              dbms_output.put_line(v_loc_id);
                              :new.location_id := v_loc_id;

                              end;
                              end;

                              solve???
                              • 12. Re: How to generate composite primary key by trigger without using sequences
                                rp0428
                                Please explain how you expect that to work when there are multiple sessions updating the same table and setting DUMMY.ID to 1 and committing at unknown different times?
                                • 13. Re: How to generate composite primary key by trigger without using sequences
                                  Solomon Yakobson
                                  Zafar Iqbal wrote:
                                  In my opinion, I have this solution
                                  Well, as others already pointed your solution will not work if multiple concurrent transactions will be inserting into your table. For example, session1:
                                  SQL> insert into x
                                    2     (company_id, location_id, account_code, created_by, time_stamp, last_updated_by, last_updated_on)
                                    3   values
                                    4     ('001', '001', '0101100001', '0010011', to_date('02/22/2013 11:49:11', 'MM/DD/YYYY HH24:MI:SS'),
                                    5      '0010011', to_date('02/22/2013 11:49:11', 'MM/DD/YYYY HH24:MI:SS'));
                                  
                                  1 row created.
                                  
                                  SQL>
                                  Now, before session 1 commits, session 2 issues:
                                  SQL> insert into x
                                    2     (company_id, location_id, account_code, created_by, time_stamp, last_updated_by, last_updated_on)
                                    3   values
                                    4     ('001', '002', '0101100001', '0010011', to_date('02/22/2013 11:49:31', 'MM/DD/YYYY HH24:MI:SS'),
                                    5      '0010011', to_date('02/22/2013 11:49:31', 'MM/DD/YYYY HH24:MI:SS'));
                                  Since transaction can't see uncommitted changes made in other transactions, session 2 has no idea session 1 inserted a row for company_id '001'. It still thinks there are no rows in table x for company_id '001' and therefore trigger will assign location_id same value '001' as it was assigned by session 1. Now session 2 is locked - it waits for session 1 to either commit or rollback. And as soon as session 1 commits:
                                  SQL> insert into x
                                    2     (company_id, location_id, account_code, created_by, time_stamp, last_updated_by, last_updated_on)
                                    3   values
                                    4     ('001', '001', '0101100001', '0010011', to_date('02/22/2013 11:49:11', 'MM/DD/YYYY HH24:MI:SS'),
                                    5      '0010011', to_date('02/22/2013 11:49:11', 'MM/DD/YYYY HH24:MI:SS'));
                                  
                                  1 row created.
                                  
                                  SQL>
                                  SQL> commit;
                                  
                                  Commit complete.
                                  
                                  SQL>
                                  Session 2 raises unique constraint violation exception:
                                  SQL> insert into x
                                    2     (company_id, location_id, account_code, created_by, time_stamp, last_updated_by, last_updated_on)
                                    3   values
                                    4     ('001', '002', '0101100001', '0010011', to_date('02/22/2013 11:49:31', 'MM/DD/YYYY HH24:MI:SS'),
                                    5      '0010011', to_date('02/22/2013 11:49:31', 'MM/DD/YYYY HH24:MI:SS'));
                                  insert into x
                                  *
                                  ERROR at line 1:
                                  ORA-00001: unique constraint (SCOTT.X_PK) violated
                                  
                                  
                                  SQL>
                                  There is one more issue with your trigger. It queries triggering table. This is allowed only when using INSERT VALUES statement. Otherwise Oracle will raise table is mutating exception:
                                  SQL> insert
                                    2    into x
                                    3    select '001', '002', '0101100001', '0010011',sysdate,'0010011',sysdate from dual
                                    4  /
                                    into x
                                         *
                                  ERROR at line 2:
                                  ORA-04091: table SCOTT.X is mutating, trigger/function may not see it
                                  ORA-06512: at "SCOTT.GENERATE_X_LOC_ID", line 5
                                  ORA-04088: error during execution of trigger 'SCOTT.GENERATE_X_LOC_ID'
                                  
                                  
                                  SQL>
                                  So your solution is very limited. It will work only for INSERT VALUES in serial mode. But even if you'll decide to live with such restrictions, you need to decide what to do if location is deleted. Then you will have holes in your numbering and you will need after delete trigger to reassign locations which can present an issue if location is referenced elsewhere. To summarize - your design seems to be flawed and you really need to review it and decide if it is justified.

                                  SY.
                                  • 14. Re: How to generate composite primary key by trigger without using sequences
                                    Christine Schnittker
                                    Apart from that it would work, you can even produce the originally requested output from it :)
                                    with company as
                                         (select 1 as company_id, 1 as location_id from dual union all
                                          select 1 as company_id, 2 as location_id from dual union all
                                          select 2 as company_id, 3 as location_id from dual union all
                                          select 1 as company_id, 4 as location_id from dual union all
                                          select 3 as company_id, 5 as location_id from dual union all
                                          select 2 as company_id, 6 as location_id from dual)
                                    select    
                                          company_id, 
                                          row_number() over (partition by company_id order by location_id) as location_id
                                    from company
                                    //Tine