The INSERT statement conflicted with the FOREIGN KEY constraint

Expand Post »

Hey All,

I am using MSSQL -2005 with VB6.
I have created a master table tblCompany and detail Table tblDetail having foreign key relationship.
When i try to insert a value within a TRANSACTION I am getting Error No. -2147217873 at Line No. 0 (The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblDetail_tblCompany". The conflict occurred in database "DBTest", table "dbo.tblCompany", column RefID.) .
please help me to solve this problem.

sql Syntax (Toggle Plain Text)
CREATE TABLE [dbo].[tblCompany](
    [RefID] [INT] IDENTITY(1,1) NOT NULL,
    [CompanyName] [VARCHAR](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Amount] [DECIMAL](18, 2) NOT NULL CONSTRAINT [DF_tblCompany_Amount]  DEFAULT ((0)),
 CONSTRAINT [PK_tblCompany_1] PRIMARY KEY CLUSTERED
(
    [RefID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
CREATE TABLE [dbo].[tblDetail](
    [DetailID] [INT] IDENTITY(1,1) NOT NULL,
    [RefID] [INT] NULL,
    [Amount] [DECIMAL](18, 2) NOT NULL CONSTRAINT [DF_tblDetail_Amount]  DEFAULT ((0)),
 CONSTRAINT [PK_tblDetail] PRIMARY KEY CLUSTERED
(
    [DetailID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[tblDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblDetail_tblCompany] FOREIGN KEY([RefID])
REFERENCES [dbo].[tblCompany] ([RefID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblDetail] CHECK CONSTRAINT [FK_tblDetail_tblCompany]
CREATE TABLE [dbo].[tblCompany](
    [RefID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Amount] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblCompany_Amount]  DEFAULT ((0)),
 CONSTRAINT [PK_tblCompany_1] PRIMARY KEY CLUSTERED
(
    [RefID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tblDetail](
    [DetailID] [int] IDENTITY(1,1) NOT NULL,
    [RefID] [int] NULL,
    [Amount] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblDetail_Amount]  DEFAULT ((0)),
 CONSTRAINT [PK_tblDetail] PRIMARY KEY CLUSTERED
(
    [DetailID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tblDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblDetail_tblCompany] FOREIGN KEY([RefID])
REFERENCES [dbo].[tblCompany] ([RefID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblDetail] CHECK CONSTRAINT [FK_tblDetail_tblCompany]

CODE :

vb Syntax (Toggle Plain Text)
Dim intID as Integer
 
adoconn.BeginTrans
with adoRsMaster
    .Open "Select * from tblCompany Where 1=2",adoconn,adOpenDynamic,adLockOptimistic   
    addnew
    !CompanyName="Sample"
    !Amount =2500
    .update
intID = !RefID
end with
with adorsDetail
    .Open "Select * from tblDetail Where 1=2",adoconn,adOpenDynamic,adLockOptimistic   
    .addnew
    !RefID = intID
    !Amount = 2500
    .update
end with
adoconn.commitTrans
Dim intID as Integer

adoconn.BeginTrans
with adoRsMaster
    .Open "Select * from tblCompany Where 1=2",adoconn,adOpenDynamic,adLockOptimistic   
    addnew
    !CompanyName="Sample"
    !Amount =2500
    .update
intID = !RefID
end with
with adorsDetail
    .Open "Select * from tblDetail Where 1=2",adoconn,adOpenDynamic,adLockOptimistic   
    .addnew
    !RefID = intID
    !Amount = 2500
    .update
end with
adoconn.commitTrans

Last edited by Ezzaral; Jul 3rd, 2009 at 1:33 pm. Reason: Added [code] [/code] tags. Please use them to format any code that you post.

Similar Threads

Foreigb Key Constraint Failing in MySQL
Foreign key constraint fails in MySQL
SQL Server 2005 version Error Insert command help me in MySQL

ksenthilbabu

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Jun 2009

Permalink

Jul 3rd, 2009

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

Please wrap your posts in [code] tags in the future.

Can you post the raw SQL you're using here? I created your tables and was able to insert OK.

sql Syntax (Toggle Plain Text)
INSERT INTO tblCompany (CompanyName, Amount) VALUES ('Sample', 2500)
DECLARE @id INT
SET @id = Cast(SCOPE_IDENTITY() as INT)
 
INSERT INTO tblDetail (RefId, Amount) VALUES (@id, 2500)
Insert Into tblCompany (CompanyName, Amount) Values ('Sample', 2500)
Declare @id int
Set @id = Cast(SCOPE_IDENTITY() as int)

Insert Into tblDetail (RefId, Amount) Values (@id, 2500)

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 4th, 2009

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

Hey sknake,
Thanks for your Reply,

I Found that the adoMaster!RefID is returning the value not from the specified table but it is returning then max of IDENTITY Value from the Database.

For Example
Table Name: tblCompany has Primary(identity) Column RefID with last Value of 10
Table Name: tblDetail has Primary(identity) Column DetailID with Last Value 20
Table Name: tblValue has primary(identity) Column ValueID with Last Value 50

After Updating tblCompany with a new record adoMaster!RefID is returning Value 51 which is the next identity Value of tblValue Table instead of returning 11 Which is the next identity Value of tblCompany.

I found this when i Inserted SQL statement after adomaster.update
SQL Statement Inserted :

MS SQL Syntax (Toggle Plain Text)
adoID.Open "Select Max(RefID) from tblCompany", adoConn, adOpenForwardOnly, adLockReadOnly
iRefID = adoID(0).Value
adoID.Open "Select Max(RefID) from tblCompany", adoConn, adOpenForwardOnly, adLockReadOnly
iRefID = adoID(0).Value

Here adoID(0).Value is returning 11 that has to be return by adomaster!RefID after Updating with new record.

Please guide me

ksenthilbabu

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Jun 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: String or binary data would be truncated.

Next Thread in MS SQL Forum Timeline: MS SQL 2005 DTS Trouble...

DaniWeb Message

Cancel Changes

User Name
Password