Foreign key constraint problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Foreign key constraint problem

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-09-16 : 05:01:51

Hi,

I've created a table with a foreign key constraint and I'm loading data into it from a temporary table within a procedure.

I've been getting a fk constraint violation error, even though I can remove the FK add the same data and put the FK back again and it is quite happy.

Below is the table definition and offending query.

I'm getting round this by adding the key at the end of the insert process, but I'd appreciate it if anyone can tell me what the problem might be.

Sean

CREATE TABLE [dbo].[pData_BinCountRollScheduleswap](
[pDataSeriesID] [int] IDENTITY(1,1) NOT NULL,
[pDataID] [int] NOT NULL,
[ContractCode] [varchar](20) NOT NULL,
[ContractID] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[aOpen] [decimal](18, 6) NOT NULL,
[aHigh] [decimal](18, 6) NOT NULL,
[aLow] [decimal](18, 6) NOT NULL,
[aClose] [decimal](18, 6) NOT NULL,
[uOpen] [decimal](18, 6) NOT NULL,
[uHigh] [decimal](18, 6) NOT NULL,
[uLow] [decimal](18, 6) NOT NULL,
[uClose] [decimal](18, 6) NOT NULL,
[eod] [bit] NOT NULL DEFAULT ((0)),
[Overlap] [bit] NOT NULL DEFAULT ((0)),
PRIMARY KEY NONCLUSTERED
(
[pDataSeriesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[pData_BinCountRollScheduleswap] WITH CHECK ADD FOREIGN KEY([pDataID])
REFERENCES [dbo].[Pdata] ([pDataID])

Query

INSERT INTO dbo.pdata_BinCountRollScheduleswap
(pDataID, ContractCode,
ContractID, Date, aOpen,
aHigh, aLow, aClose,
[uOpen], [uHigh], [uLow],
[uClose], [overlap] )
SELECT pDataID, ContractCode,
ContractID, Date, aOpen,
aHigh, aLow, aClose,
[uOpen], [uHigh], [uLow],
[uClose], [overlap]
from #allticks3

Error message
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__pData_Bin__pData__4D5128F5". The conflict occurred in database "FuturesBin", table "dbo.Pdata", column 'pDataID'.
The statement has been terminated.

Sean

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-09-16 : 05:10:47

Because the pdataid that you trying to enter via the

SELECT pDataID, ContractCode,
ContractID, Date, aOpen,
aHigh, aLow, aClose,
[uOpen], [uHigh], [uLow],
[uClose], [overlap]
from #allticks3

statement is not held in the [dbo].[Pdata] table also.

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-09-16 : 05:23:48

I've checked that it is, and in fact I can remove the constraint add the data and put the constraint back and it works, which should also fail if the pdataid was missing.

Sean

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-16 : 13:24:41

quote:
Originally posted by Sean_B

I've checked that it is, and in fact I can remove the constraint add the data and put the constraint back and it works, which should also fail if the pdataid was missing.

Sean

are you adding constraint with no check option?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources