TSQL Insert Data Linked by Foreign Key

Question

Let's say I have two tables

RDB_DataEntities

DataEntityId
Name
Created
Modified
...

RDB_DataInstances

DataInstanceId
DataEntityId

So RDB_DataInstances is joined to RDB_DataEntities via a foreign key on their DataEntityId columns.

Let's say I want to insert data into both tables in the same transaction. The code I have for this is as follows:

using (var con = new SqlConnection("data source=speedy;initial catalog=mydb;user id=myuser;password=mypass"))
{
   con.Open();

   using (var tran = con.BeginTransaction())
   {
      SqlCommand i1 = new SqlCommand("insert into RDB_DataEntities (Name,IsSchema,Created,Modified,RequireCaptcha,UniqueByEmail,UniqueByMac) values ('hi',0,GetDate(),GetDate(),0,0,0)", con, tran);

      i1.ExecuteNonQuery();

      SqlCommand i2 = new SqlCommand("select SCOPE_IDENTITY() as newid", con, tran);
      var id = int.Parse(i2.ExecuteScalar().ToString());

      SqlCommand i3 = new SqlCommand("insert into RDB_DataInstances (DataEntityId) values (" + id + ")", con, tran);
      i3.ExecuteScalar();
      tran.Commit();
   }
}

Why is it throwing a foreign key error

The INSERT statement conflicted with the FOREIGN KEY constraint 'FK_RDB_DataInstances_RDB_DataEntities'. The conflict occurred in database 'NMSS_CMS', table 'dbo.RDB_DataEntities', column 'DataEntityId'.

Shouldn't the transaction know that I am inserting the foreign key based upon an insert I just did within the current transaction context? Am I way off?

How do you make this happen?

It's not happening on your first `INSERT` is it? That is, you haven't set up the FK the wrong way around... — ta.speot.is, Mar 27 '12 at 03:41

mgnoonan · Answer 1 · 2012-03-26T21:06:28.637

1

Because SCOPE_IDENTITY() will not work when you are using two different command contexts (by definition it is a separate scope). You can append the second query onto the first, and run the ExecuteScalar(), like so:

SqlCommand i1 = new SqlCommand("insert into RDB_DataEntities (Name,IsSchema,Created,Modified,RequireCaptcha,UniqueByEmail,UniqueByMac) values ('hi',0,GetDate(),GetDate(),0,0,0);select SCOPE_IDENTITY() as newid;",con,tran);                 
var id=int.Parse(i1.ExecuteScalar().ToString());

Edit: Just wanted to throw together the T-SQL version of what's going on.

DECLARE @newid int

BEGIN TRANSACTION

insert into RDB_DataEntities 
    (Name,IsSchema,Created,Modified,RequireCaptcha,UniqueByEmail,UniqueByMac) 
values 
    ('hi',0,GetDate(),GetDate(),0,0,0)

SELECT @newid = SCOPE_IDENTITY()

insert into RDB_DataInstances 
    (DataEntityId) 
values 
    (@newid)

COMMIT TRANSACTION

edited Mar 26 '12 at 21:06

answered Mar 26 '12 at 20:52

mgnoonan

7,060
5
24
27

2

Another solution: use the output clause. Saves yourself a query. – ta.speot.is Mar 26 '12 at 20:53
SCOPE_IDENTITY() definitely works both ways. I've debugged to ensure that my var id definitely has an integer value. – Micah Mar 26 '12 at 20:55
If you are getting the correct value back, then I'm in agreement that it should work. The T-SQL version of what you are trying to do makes logical sense. – mgnoonan Mar 26 '12 at 21:02

score 0 · Answer 2 · edited May 23 '17 at 11:56

Here's 2 possible solutions for you if I've understood your problem:

You could set the transaction isolation level and use READ UNCOMMITTED / NOLOCK to perform a dirty read on the previously inserted record in the transaction (See this post for more Why use a READ UNCOMMITTED isolation level?)
You could just create the Guid in code and manually insert into the 2 tables as a SqlCommand parameter rather than using SCOPE_IDENTITY() after the first insert. (My preferred option)

score 0 · Answer 3 · answered Mar 27 '12 at 21:18

0

I dropped the foreign key and recreated it and it started working.

answered Mar 27 '12 at 21:18

Micah

10,295
13
66
95

TSQL Insert Data Linked by Foreign Key

3 Answers3