Obtaining Auto-Generated Primary Key Values for Use as Foreign Keys

All we need is an easy explanation of the problem, so here it is.

I am running a local instance of Microsoft SQL Server 15 (2019) and am using Microsoft SQL Server Management Studio v18.9.1 as the client to work through the process of creating the tables needed for my application.

This is all running under Windows 10.

Tables

Suppose I have the tables defined below.

CREATE TABLE dbo."Table_1"
(
   "SomeData" VARCHAR(50) NOT NULL,
   "Table_1_SurrogateKey" BIGINT IDENTITY(1,1) NOT NULL

   CONSTRAINT PK_Table_1 PRIMARY KEY ("Table_1_SurrogateKey")
);

CREATE TABLE dbo."Table_2"
(
   "Table_2_PrimaryKey" VARCHAR(10) NOT NULL,
   "Table_1_SurrogateKey_A" BIGINT NOT NULL,
   "Table_1_SurrogateKey_B" BIGINT NOT NULL,

   CONSTRAINT PK_Table_2 PRIMARY KEY ("Table_2_PrimaryKey"),

   CONSTRAINT FK_Table_2_Table_1_SurrogateKey_A FOREIGN KEY ("Table_1_SurrogateKey_A") REFERENCES dbo."Table_1"("Table_1_SurrogateKey"),
   CONSTRAINT FK_Table_2_Table_1_SurrogateKey_B FOREIGN KEY ("Table_1_SurrogateKey_B") REFERENCES dbo."Table_1"("Table_1_SurrogateKey"),
   CONSTRAINT CHK_Table_1_SurrogateKey_A_NE_Table_1_SurrogateKey_B CHECK ("Table_1_SurrogateKey_A" != "Table_1_SurrogateKey_B")
);

A side note: Each row in "Table_2" always has two associated rows in "Table_1". Therefore, this is properly normalized.

INSERT Statements

Suppose I execute the INSERT statements shown below.

-- Primary keys for "Table_1" are auto-generated and thus do not appear explicitly in INSERT statements
INSERT INTO dbo."Table_1" ("SomeData") VALUES ('foo');
INSERT INTO dbo."Table_1" ("SomeData") VALUES ('bar');

-- Primary keys for "Table_2" are not auto-generated and thus must appear explicitly in INSERT statements
INSERT INTO dbo."Table_2" ("Table_2_PrimaryKey", "Table_1_SurrogateKey_A", "Table_1_SurrogateKey_B")
                          VALUES ('A16922', ???, ???);

As indicated by the ??? values given for the second and third fields of the last INSERT statement above, I am unsure how to determine the needed value of the auto-generated primary keys from the first two INSERT statements above.

Related Experience (postgres)

When working with postgres, I found the following technique to solve this problem:

INSERT INTO dbo."Table_2" ("Table_2_PrimaryKey", "Table_1_SurrogateKey_A", "Table_1_SurrogateKey_B")
                          VALUES (
                                    'A16922',
                                    currval('"Table_1_Table_1_PrimaryKey_seq"')-1,
                                    currval('"Table_1_Table_1_PrimaryKey_seq"')-0
                                 );

Here, table "Table_1_Table_1_PrimaryKey_seq" was automatically generated by postgres to track the sequence of primary keys that had been auto-generated.

This does not work when using Microsoft SQL Server. The following error is given:

Msg 515, Level 16, State 2, Line 91 Cannot insert the value NULL into
column ‘Table_1_SurrogateKey_A’, table ‘My_DB.dbo.Table_2’; column
does not allow nulls. INSERT fails.

So, I am having difficulty finding the right way to do this in a SQL Server environment.

A Near Solution

SCOPE_IDENTITY() seems close to what I need. So, I tried the following:

INSERT INTO dbo."Table_2" ("Table_2_PrimaryKey", "Table_1_SurrogateKey_A", "Table_1_SurrogateKey_B")
                          VALUES (
                                    'A16922',
                                    SCOPE_IDENTITY()-1,
                                    SCOPE_IDENTITY()-0
                                 );

My Question (Microsoft SQL Server)

When working with Microsoft SQL Server, how may I obtain the auto-generated primary keys for the two rows inserted in "Table_1"? As can be seen, they are needed as foreign keys for insertion of a row into related table "Table_2".

Please note that the solution needs to be backwards-compatible with Microsoft SQL Server 13 (2016).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You are really close with your solution using SCOPE_IDENTITY(). However, your code assume no other transaction inserted a row in Table_1 between your 2 INSERTs. To avoid this issue, you could do something like this:

DECLARE @id1 INT;
DECLARE @id2 INT;
INSERT INTO dbo."Table_1" ("SomeData") VALUES ('foo');
SET @id1=SCOPE_IDENTITY();
INSERT INTO dbo."Table_1" ("SomeData") VALUES ('bar');
SET @id2=SCOPE_IDENTITY();

INSERT INTO dbo."Table_2" ("Table_2_PrimaryKey", "Table_1_SurrogateKey_A", "Table_1_SurrogateKey_B")
                          VALUES ('A16922', @id1, @id2);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply