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

Question

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

mr_adeelalisyed

SSC-Addicted

Points: 470
More actions
November 17, 2009 at 8:42 am

#96169

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You did not include the foreign key definitions with your table scripts. Based on what you did share, I would guess that the problem is that the CapturedDate you are inserting does not match the CapturedDate in [dbo].[TotalDatabase]. Can you share the code that populates the temp table and how [dbo].[TotalDatabase] gets loaded?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question