Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Unanswered: The INSERT statement conflicted with the FOREIGN KEY

    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

  2. #2
    Join Date
    Jun 2009
    Location
    CA, USA
    Posts
    59
    It means exactly what it says....
    When you have the FK, you have to follow the rules...
    Before inserting into child table it has to exist in parent table...
    Make sure you have the record in tblcompany table before inserting into tbldetail table...
    MohammedU
    SQL Server MVP

  3. #3
    Join Date
    Jul 2009
    Posts
    5
    Hey Mr. Mohammedu,

    Thanks for your reply.

    I am only inserting into the detail table (tblDetail) after getting the Primay key value from the Primary table (tblCompany).

    Primary Key tABLE : tblCompany
    Detail Table : tblDetail


    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
    The above code is working fine with MSACCESS, MYSQL and ORACLE. but it is not working with MSSQL2005.

    I found after analysing that adoMaster!RefID is generating @@IDENTITY Value instead of to return @@SCOPE_IDENTITY.

    Please help me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •