This discussion is archived
14 Replies Latest reply: Feb 23, 2013 5:32 AM by Christine Schnittker RSS

How to generate composite primary key by trigger without using sequences

Zafar Iqbal Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points