The INSERT statement conflicted with the FOREIGN KEY constraint

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.


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 :

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

re: The INSERT statement conflicted with the FOREIGN KEY constraint

Display all the values you're inserting on your detail table. You might have RefID as NULL.

--- CK

re: The INSERT statement conflicted with the FOREIGN KEY constraint

First you have to check NULL values for primary key.

And now my suggestion is that you need to create two DIFFRERENT TRANSACTIONS to insert/update in parent and child tabels.

Not sure but when you create a transaction and insert/update any rows in parent tables, these are not inserted until you have not COMMIT the transaction.
Because of that when you try to insert rows in child table it does'nt found the parent row.

So,you can commit parent table transaction ,create new transaction for child table then commit child table transaction.

OR simply if it will not affect your requirement REMOVE transaction from code.


Hope this will work.

HAPPY CODING...

-Pankaj Tambe