Problem solve Get help with specific problems with your technologies, process and projects.

The surrogate key of the row just inserted

How do I insert foreign key values in SQL? I have two tables, and in the first table is a primary key, and in the second a foreign key of that primary key. When I insert the row for the second table, how do I get the foreign key values, which obviously must be the same as the primary key values in the first table?

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.

Dig Deeper on Oracle and SQL