INSERT statement conflicted with the FOREIGN KEY c

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

INSERT statement conflicted with the FOREIGN KEY c

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-12 : 10:02:21

I am getting this error occasionally on my multi-user web app with the data stored in SQL Server. The key is an identity field incremented by 1 - like Access autonumber. It seems that when two people are writing to the table at the same time they try and use the same key. I didn't think this could happen in sql server ?

It is a one to many table situation and the error occurs in the one table

What is the solution please.

INSERT statement conflicted with the FOREIGN KEY constraint

Kristen
Test

22859 Posts

Posted - 2006-12-12 : 10:11:30

"identity field incremented by 1"

Do you mean that the field has the IDENTITY property, and is incremented by SQL Server itself, or that you are doing the incrementing - by finding the next number and then using that?

If IDENTITY are you using @@IDENTITY or SCOPE_IDENTITY to find the Identity number that has just been allocated?

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-12 : 10:42:44

Sorry, I have misled you. In my one table I am getting the number from a table,incrementing the number and immediately writing it back to the table before the record is entered and saved.This makes the next number available for anyone else to use. Here's the sp that generates it

CREATE PROCEDURE spRMU_GetNextRequestNo

@NextReqNo int OUTPUT

as

update tblCounterTable
set @NextReqNo = NextRequestNo + 1,
NextRequestNo = @NextReqNo

return @NextReqNo
GO

In my many table the key has the identity property and SQL server increments it itself by 1. I am not returning this number to the web page.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-12 : 10:46:59

so why aren't you using identity?

yes 2 users can get the same number if the timing is bad...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-12 : 10:51:21

Will it make a difference if I return identity ? How do I amend my sp for this ?

Is there a work around the two users thing as I have about 40 users. Should I generate the number on the many table too ?

This project has only gone live the last 6 days and this has happened twice (that I know of !)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-12 : 10:54:47

i have no idea how it will affect your system since i'm not familiar with it.
last identity inserted in the scope is
select scope_identity()

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-12 : 11:09:28

I apologise if I misunderstand, but how will returning the identity from my many table stop two different users grabbing the same number if they write to it at the same time ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-12 : 11:25:13

you don't have to grab anything that's the beauty of identity. it self increments.

maybe you shoul dpost the table structure and your statement flow so we can get a clearer picture of what
you're trying to do.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-12 : 11:31:01

Thanks I will do that tomorrow as it is time to go home now - thank goodness :-)

Kristen
Test

22859 Posts

Posted - 2006-12-12 : 14:25:32

"Sorry, I have misled you."

Nah, no worries. Its a well-enough constructed question, and you are not Ernest about the responses! That will do for me!

There are two ways of doing this:

1) You can insert into your data Table with an IDENTITY column. SQL Server will allocate the next-available-number to the identity column (guaranteed no chance of a duplicate value being assigned), you can retrieve the Identity value [with SCOPE_IDENTITY()] and then use that to create the Children records, with no FK issues.

2) You can store calculate the "next available number" [either by storing it in a one-row-table and querying-&-incrementing it when you assign a new value, or by using a SELECT MAX(MyID)+1 FROM MyTable], but you have to be careful to "lock" the table for the duration of the:

Get the last-used-number
Add one
Insert using next-number

transactions.

Both strategies are equally valid, but IDENTITY is easier to implement.

However, right now [in my application] I am getting deadlock-victims on the Second Method and I'd appreciate opinions on the best way to implement the method of:

Get MAX value from "NextIDTable"
Increment
Write back
Create new record

with appropriate locking and transaction.

Thanks!

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-12 : 14:47:17

Kristen - I don't understand why you would want to use option 2, when sql server will take care of this for you if you use IDENTITY.

Can you give an example where option 2 is preferred over identity?

http://www.elsasoft.org

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-12-12 : 17:14:52

Grrrr I remember reading about this on a MS bvlog page, anyone? Ill keep looking.

But in ther mean time, you need this trick when:
1. you need a series of unbroken consecutive number (and IDENTITY dont give you that), but you will have to sacrifice concunrency.
2. you need the number ahaed of creating the 'master' record (this will of course prohobit FK constraints).

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

Kristen
Test

22859 Posts

Posted - 2006-12-13 : 04:33:39

"I don't understand why you would want to use option 2"

We use it to allocate "resources" which are server-specific, and which are later merged with data from other sites.

It can also be handy where you want to allocate IDs to a batch of records, perhaps ahead of them actually being saved to the database.

Its "slimmer" than a GUID for multi-site usage.

However, for any straightforward "insert one and get the next available number" scenario I would use IDENTITY

Kristen

Kristen
Test

22859 Posts

Posted - 2006-12-13 : 05:46:14

I reckon this is fail-safe (i.e. won't generate duplicate allocations):


CREATE TABLE tblCounterTable
(
	NextRequestNo int
)
GO
INSERT tblCounterTable SELECT 1	-- Starter number
GO
--DEBUG SELECT * FROM tblCounterTable
GO
DECLARE @intRowCount	int,
	@NextReqNo	int
SET @intRowCount = 0 -- Force first iteration
WHILE @intRowCount = 0
BEGIN
	SELECT	@NextReqNo = NextRequestNo
	FROM	tblCounterTable
--DEBUG	WAITFOR DELAY '00:00:02'
	UPDATE	tblCounterTable
	SET	NextRequestNo = @NextReqNo + 1
	WHERE	NextRequestNo = @NextReqNo
	SELECT @intRowCount = @@ROWCOUNT
--DEBUG SELECT 'loop', * FROM tblCounterTable
END
--DEBUG SELECT * FROM tblCounterTable

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-13 : 06:37:45

tblRequests

MovementId Key, No identity. Number generated from tblCountertable in similar way to Kirsten's
Other field1..
Other field2....

tblFieldRequestDetails

ReqId Key Identity Field Increments by 1 automatically
MovementId Field linking many table with one table
OtherField1
OtherField2...

I believe the error message is occurring when the record is written to tblFileRequestDetails and the id is automatically generated.
This application originally was written in Access and had no problems. In SQL Server, it takes longer to write to the table and this is why I think the problem is occurring. Table design is the same between old and new system - ie the ids and number generating

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-12-13 : 18:00:00

found the link: http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-12-13 : 19:14:43

quote:
CREATE PROCEDURE spRMU_GetNextRequestNo

@NextReqNo int OUTPUT

as

update tblCounterTable
set @NextReqNo = NextRequestNo + 1,
NextRequestNo = @NextReqNo

return @NextReqNo
GO

quote:
yes 2 users can get the same number if the timing is bad...

I don't see how that is possible?!
Can concurrent calls to spRMU_GetNextRequestNo really get the same number?

And referring to PSamsig's article, would these be different with regards to any possibility of returning the same value?

update tblCounterTable
set @NextReqNo = NextRequestNo + 1,
NextRequestNo = @NextReqNo

update tblCounterTable
set @NextReqNo = NextRequestNo = NextRequestNo + 1

I don't think so..

In the article there was a mention of lock/deadlocking problems for this method though.

Here is another interesting article:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/15/945.aspx

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources