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

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');

The last Identity value can be obtained in SQL Server 2000 by calling @@identity.

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

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.

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

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

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

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!)

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 -- 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

Good point! And the fact that it almost forces you to define a "proper" unique key can only be good!

Bravo, r937!

fyi
@@identity is not thread safe, scope_identity() is much better.