Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: FOREIGN KEY constraint Error. What should be change in which table structure

    Hello experts,



    I’m trying to write a code to insert data in one of the table ‘TotalTable’. First I’ve created a temp table ‘#TempTotalTable’ and popluate it with all the data I needed. Everything works fine upto here and I’ve been able to pull and hold data into ‘#TempTotalTable’. Now I need to insert this data in‘TotalTable’. I wrote the following query to insert data from ‘#TempTotalTable’ into ‘TotalTables’



    Here is the query

    [ Insert into dbo. TotalTable

    select TBId, TBName, CapturedDate, DBName, TbCreationData, TbLastModifiedDate, NoOfCol

    from #TempTotalTable

    order by tbid

    ]



    But end up having the following error.

    [The INSERT statement conflicted with the FOREIGN KEY constraint "Per_Database". The conflict occurred in database "CIAdeel", table "dbo.TotalDatabase".

    The statement has been terminated.]



    I realize that the error is about conflicting fogeign key which I’ve in anyother table ‘TotalDatabase’. But I’ve already made sure all the entries of ‘DBName field’ in ‘#TempTotalTable’ which I’m trying to insert in ‘TotalTable’ are already exist in ‘TotalDatabase’ table. Because ‘DBName’ and ‘CapturedDate’ are the primary keys in ‘TotalDatabase’ and fogeign keys in ‘TotalTable’ table. Now I’m not sure how to resolve this issue.



    For reference here are the sturctures of ‘#TempTotalTable’, ‘TotalTables’ & ‘TotalDatabase’ tables.



    Create Table #TempTotalTable(

    DBname varchar ( 100),

    TBId int ,

    TBName varchar ( 500),

    CapturedDate datetime ,

    TBCreationData datetime ,

    TBLastModifiedDate datetime ,

    NoOfCol int

    )



    CREATE TABLE [dbo]. [TotalTable](

    [TbId] [int] NOT NULL,

    [TbName] [varchar]( 500) NOT NULL,

    [CapturedDate] [datetime] NOT NULL,

    [DbName] [varchar]( 100) NOT NULL,

    [TbCreationDate] [datetime] NULL,

    [TbLastModifiedDate] [datetime] NULL,

    [NoOfCol] [int] NULL,

    CONSTRAINT [XPKTotalTable] PRIMARY KEY NONCLUSTERED

    (

    [TbId] ASC ,

    [TbName] ASC

    ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

    ) ON [PRIMARY]





    CREATE TABLE [dbo]. [TotalDatabase](

    [DbName] [varchar]( 100) NOT NULL,

    [DbCreationDate] [datetime] NULL,

    [CapturedDate] [datetime] NOT NULL,

    [NoOfTable] [int] NULL,

    CONSTRAINT [XPKTotalDatabase] PRIMARY KEY NONCLUSTERED

    (

    [CapturedDate] ASC ,

    [DbName] ASC

    ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

    ) ON [PRIMARY]





    Can anybody tell what changes I should make and which table to insert data into ‘TotalTable’ table.



    Thanks a lot in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Since you didn't the script the constraints, I can only hazard a guess that [dbname] is a foreign key in the [TotalTable] table (bad naming practices btw).

    Which MEANS, that a value in your temp table has a dbname that does not exist in the [TotalDatabase] Table...hence tyhe FK error

    SELECT DISTINCT dbname FROM #TempTotalTable t
    WHERE NOT EXISTS (
    SELECT * FROM TotalDatabase d
    WHERE t.dbname = d.dbname)

    The above will give you a list of dbnames that need to be in the TotalDatabase table BEFORE you can insert the rows into TotalTable
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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