Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Location
    USA (East Coast)
    Posts
    6

    Question using auto-increment (identity) fields

    Problem:
    ----------
    I inserted new record into a table. I need to use new primary key as a foreign key to insert dependent records into other table(s).

    Solution for Oracle or DB2:
    -------------------------------
    In Oracle or DB2 there is such thing as a sequence object. I request 'nextval' from the sequence, and use it as both primary key for my primary table and foreign key for my dependent table(s).

    Question:
    -----------
    How to do this in SQL Server, Access, MySql, etc.? These databases do not have sequences. They use auto-increment (auto-number, identity, etc.) fields. The value is assigned at the insertion time, I do not know what the value is and cannot use it as a foreign key.

    Thanks in advance,

    Alex

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Create table A
    (
    id Integer IDENTITY(1, 1),
    name varchar(10),
    Constraint A_PK primary key(id)
    )

    Create table B
    (
    id Integer,
    name varchar(10),
    Constraint B_FK_ID foreign key (id) references a(id)
    )
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by r123456
    Create table A
    (
    id Integer IDENTITY(1, 1),
    name varchar(10),
    Constraint A_PK primary key(id)
    )

    Create table B
    (
    id Integer,
    name varchar(10),
    Constraint B_FK_ID foreign key (id) references a(id)
    )
    Yes, but I think Alex meant: how do you find out the value of A.ID just inserted when you go on to insert into B?

    insert into A (name) values ('smith');
    insert into B (id, name) values (?????, 'jones');

    Presumably these DBMSs have a "get_last_identity_used" function or something? Or do they support a RETURNING clause on insert like Oracle does? :-

    insert into A (name) values ('smith') returning id into v_id;
    insert into B (id, name) values (v_id, 'jones');

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The last Identity value can be obtained in SQL Server 2000 by calling @@identity.
    Code:
    Insert into a values (D)
    Insert into a values (E) 
    Select * from a where id > 3;
    
    ID   VAR
    ---  ------
     4      D
     5      E
    
    Select @@identity
    = 6
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Location
    USA (East Coast)
    Posts
    6

    Question

    What is the scope of @@identity variable - global, user session? Is it safe to use this in multi-user environment? I believe it is.

    But this is SQL Server... I am looking for one cross-database uniform way of doing this.

    First scenario works for Oracle and DB2 (sequence object):

    1. Obtain new unique id.
    2. Insert new record into primary table using obtained id as a primary key.
    3. Insert records into dependent tables using obtained id as a foreign key.

    Second scenario works for SQL Server (select @@identity):

    1. Insert new record into primary table.
    2. Obtain primary key value of just inserted record.
    3. Insert records into dependent tables using obtained primary key value as a foreign key.

    I am not sure what scenarios are for MS Access, MySQL, and other databases. Is there a universal way of doing this?

    -Alex

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    No, there is no standard way that applies across the various DBMSs. Best to encapsulate your (insert row and get generated PK) logic inside a procedure which can then be re-implemented for each DBMS. Preferably a stored procedure, if all your DBMSs support them.

  7. #7
    Join Date
    Feb 2004
    Location
    USA (East Coast)
    Posts
    6

    Unhappy

    That's a shame. What I am trying to do is very common task. It should be standardized across databases.

    For my purpose I will use sequence object if database provides it, and simulate sequence if database does not provide it. For example, I can create SEQUENCES table with SEQUENCE_NAME (unique), and SEQUENCE_VALUE, and have a procedure that controls this table and returns next value for specified name. This should work universally.

    Thank you for your help.

    Alex

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Universally, but not very scalably perhaps. Presumably the SEQUENCES row for the SEQUENCE_NAME will be updated in the same transaction that is performing the insert - in which case, no one else can insert into the same table until the current user commits.

    Idea: how about having a dummy table with an autogenerated ID column instead of the SEQUENCES table? The the get_next_id procedure would be something like:

    begin
    insert into dummy table (dummy) values ('x');
    select @@identity;
    end

  9. #9
    Join Date
    Feb 2004
    Location
    USA (East Coast)
    Posts
    6
    But what is the difference between your way and my way in terms of scalability? Why does my 'get next value' create a bottleneck if executed within one transaction but yours does not? You just changed the algorithm of generating the key - used insert instead of update. Or I am missing something.

    -Alex

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    What you are missing is that the insert into a dummy table does not lock anything, so n>1 users can call it concurrently. The provision of the autoincrement value by the DBMS does not form part of the transaction (I sincerely hope and believe!)

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by andrewst
    No, there is no standard way that applies across the various DBMSs.
    actually, there is

    it comes from the realization that the autonumber or sequence or identity is a surrogate key, and there is some other candidate key in the table that also provides uniqueness, even if this consists of all the other columns in the table

    (and as an aside, if there is no other column or set of columns that are unique, then you have a deficient design -- but that's a completely nuther thread)

    so, do your isert, but hold on to the values of all the candidate key columns

    then, just query the row back using those values

    for example, let's say the employee table has an identity field for the employee number --
    PHP Code:
    insert 
      into employees 
         
    fname
         
    lname
         
    email
         
    dob
         
    )
    values 
         
    'Joe'
         
    'Example'
         
    'joe@example.com'
         
    '1971-03-04'
         
    )

    select employeeno 
      from employees
     where fname 
    'Joe'
       
    and lname 'Example'
       
    and email 'joe@example.com'
       
    and dob   '1971-03-04' 
    some people will see this and say "eewww, an extra SELECT step" but i would like to point out that

    1. it works in all DBMSs, and

    2. it does not require any locking or a transaction block, i.e. it does not matter how many other employees were added to the table in between my INSERT and SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by r937
    actually, there is
    ...
    Good point! And the fact that it almost forces you to define a "proper" unique key can only be good!

  13. #13
    Join Date
    Mar 2003
    Location
    Australia
    Posts
    59
    Bravo, r937!

  14. #14
    Join Date
    Feb 2004
    Posts
    2
    Originally posted by andrewst
    Universally, but not very scalably perhaps. Presumably the SEQUENCES row for the SEQUENCE_NAME will be updated in the same transaction that is performing the insert - in which case, no one else can insert into the same table until the current user commits.

    Idea: how about having a dummy table with an autogenerated ID column instead of the SEQUENCES table? The the get_next_id procedure would be something like:

    begin
    insert into dummy table (dummy) values ('x');
    select @@identity;
    end
    fyi
    @@identity is not thread safe, scope_identity() is much better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •