|
Bottom line: the best method depends on your database. See
Retrieving last row inserted (24 April 2002)
for an overview of the situation. Use SQL Server's
@@IDENTITY or MySQL's
mysql_insert_id()
or whatever the equivalent function is in your database system.
Without a database function to return the surrogate key value
of the row just inserted, you must find this value yourself.
Rule #1: do not SELECT MAX(column1) FROM theTable
The above-mentioned article had an example about a guy named
Joe Bfstplk, showing how to query back the the surrogate key
of the row just inserted using this query:
select max(column1)
from theTable
where column2 = 'Joe Bfstplk'
The first thing to notice is the WHERE clause. The query examines
only Joe Bfstplk rows, and from those rows, it picks the highest
column1 value. The idea here is that column1 is the
primary key, a surrogate autonumber, while column2 is merely
a potential candidate key.
My purpose in bringing this up is not to resurrect the
surrogate versus natural key debate. Let's just take it
as a given, that column1 is a surrogate key.
Perhaps column2 is not unique. That's why we select
max(column1), with the assumption that
the latest row has the highest value.
If you can declare a unique constraint on column2, then you can
just select column1, because there will be only one row with the
given column2 value. If it's unique, column2 could be named the primary key,
but perhaps there are good reasons why the surrogate column1 is needed.
And what if column2 is not unique, and you do select max(column1)?
Isn't it conceivable that someone else could insert another row with the
same value for column2 after yours but before your select max has executed?
Yes. And only you can assess whether this is a safe
risk for your application.
It basically comes down to whether the table has a candidate key. If there
is one, you don't have a problem. Just query the surrogate key value back using
the candidate key value(s). Don't forget, a candidate key must, by definition,
be unique, but may consist of multiple columns, even if they are all the columns
in the table (sans autonumber, of course).
For example, you might decide that the combination of first name, last name,
birthdate, and mother's maiden name are a satisfactory candidate key, which
means that (1) none of them can be null, and (2) all combinations are unique.
So right after you submit the INSERT statement,
just do a SELECT using those same values. MAX() not
required, because nobody else could have inserted
another row with the same candidate key, because it's unique.
|
