CODEWITHSUNDEEP
c#sql-serversql-server-2008ado.nettransactions
Anonymous
Anonymous

Reputation: 9648

INSERT data into two tables failed because FK violation

I created a stored procedure like below:

INSERT DATA INTO TABLE_1 VALUES ('xx', 'yy', 'zz')
SET @id = @@identity

INSERT DATA INTO TABLE_2 VALUES (@id, 'text') -- throws exception

I call it from C# code, it wrapped inside an SqlTransaction. The problem is, it always throws The INSERT statement conflicted with the FOREIGN KEY constraint in the second INSERT.

I debugged this stored procedure and it shown that the error came from the second insert statement. @id is set correctly.

I already insert data in TABLE_1 and I get the new id. I don't understand why it still throws an exception.

Update

Table structure:

Table_1
id(int) PK
name_1 varchar(10)
name_2 varchar(10)
name_3 varchar(10)

Table_2
id(int) PK, FK to Table_1.id
text_1 varchar(10)

Upvotes: 0

Views: 164

Answers (1)

andri
andri

Reputation: 1021

Some suggestion :

  1. Try specifying column name on your INSERT statement, so things doesn't get messed up when you re-arrange the column order.
  2. Use SCOPE_IDENTITY() instead of @@IDENTITY to retrieve the newly generated identity. You can read about their differences here.

Upvotes: 1

Related Questions