Primary Key Constraint error!!

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

General SQL Server Forums

New to SQL Server Programming

Primary Key Constraint error!!

Author

Topic

kiranvmysore
Starting Member

4 Posts

Posted - 2010-10-06 : 06:09:15

Hi ,
I'm trying to insert records into a table , i'm fetching data from another table. But when i do this, i'm getting a PRIMARY KEY error. I made a INTERSECT between the 2 tables to check if there are any common records between the two tables, but the INTERSECT returned zero rows. Can somebody pls help me here.Need your expertise in here.

KV

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 06:18:51

We can't really help unless you post the definition of the tables involved.

Did you do an INTERSECT on the primary key column only? INTERSECT will only show you a result set where every column matches in the SELECT parts.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kiranvmysore
Starting Member

4 Posts

Posted - 2010-10-06 : 06:49:00

I'm making an Intersect on the primary key. Both tables are of same structure, only that the table to which i'm inserting data to has 2 columns clustered to be primary.

KV

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 07:00:34

Can you post the table definitions of the tables?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 07:08:59

Try a query joining on the key columns or using EXISTS to show the duplicates. There will be duplicates.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kiranvmysore
Starting Member

4 Posts

Posted - 2010-10-06 : 07:27:57

Table1: To this table i'm inserting data into.

USE [ConsolidationDB]
GO
/****** Object: Table [dbo].[DataIn] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DataIn](
[InterfaceId] [int] NOT NULL,
[DataInId] [varchar](255) NOT NULL,
[SourceMessageId] [varchar](255) NULL,
[ReceiptTime] [datetime] NULL,
[LastProcessingStatus] [varchar](255) NULL,
[LastProcessingStatusTime] [datetime] NULL,
[SourceTrackingId] [varchar](255) NULL,
[GatewayID] [int] NULL,
CONSTRAINT [PK_Data_In] PRIMARY KEY CLUSTERED
(
[InterfaceId] ASC,
[DataInId] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DataIn] WITH CHECK ADD CONSTRAINT [FK_Data_In_Interface_InterfaceId] FOREIGN KEY([InterfaceId])
REFERENCES [dbo].[EAIInterfaces] ([InterfaceId])
GO
ALTER TABLE [dbo].[DataIn] CHECK CONSTRAINT [FK_Data_In_Interface_InterfaceId]

Table2: Here is where i'm pulling data from

USE [ConsolidationDB]
GO
/****** Object: Table [dbo].[DataIn] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DataIn_Rep](
[InterfaceId] [int] NOT NULL,
[DataInId] [varchar](255) NOT NULL,
[SourceMessageId] [varchar](255) NULL,
[ReceiptTime] [datetime] NULL,
[LastProcessingStatus] [varchar](255) NULL,
[LastProcessingStatusTime] [datetime] NULL,
[SourceTrackingId] [varchar](255) NULL,
[GatewayID] [int] NULL,
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

KV

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 07:33:09

cool.

Can you try running this:


SELECT
	old.*
FROM
	DataIn_Rep AS old
WHERE
	EXISTS (
		SELECT 1
		FROM DataIn AS new
		WHERE
			new.[InterfaceId] = old.[InterfaceId]
			AND new.[DataInId] = old.[DataInId]
		)

If that doesn't bring back any results can you please post the SQL you were trying to run to insert into the table?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 07:37:39

To check for duplicates in the source table


SELECT
	old.*
FROM
	dataIn_Rep AS old
	JOIN (
		SELECT
			di.[InterfaceId]
			, di.[DataInId]
		FROM
			dataIn_rep AS di
		GROUP BY
			di.[InterfaceId]
			, di.[DataInId]
		HAVING
			COUNT(di.[InterfaceID]) > 1
		)
		AS dups ON
			dups.[interfaceID] = old.[interfaceID]
			AND dups.[dataInId] = old.[dataInId]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kiranvmysore
Starting Member

4 Posts

Posted - 2010-10-06 : 23:43:42

I made a self join on the source , and it has duplicates. I was aware of this earlier. But when i insert i'm fetching SELECT DISTINCT. The insert statement is like below:

INSERT INTO dbo.DataIn(InterfaceId,DataInId,SourceMessageId,GatewayId,ReceiptTime,LastProcessingStatus,LastProcessingStatusTime,SourceTrackingId)
SELECT DISTINCT InterfaceId,DataInId,SourceMessageId,GatewayID,ReceiptTime,LastProcessingStatus,LastProcessingStatusTime,SourceTrackingId
FROM [DataIn_Rep] with (nolock) WHERE Exists ='N'
<<i forgor to mention that there is column by name "Exists" in the source table in the previous post>>

KV

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-07 : 04:17:33

It's still possible for that SELECT DISTINCT to return multiple copies of the primary key if the other columns are different.

I don't know what to tell you.

Unless there is a trigger on the destination table you aren't telling us about the either.

1) you are trying to insert a duplicate (a row already exists with the same primary key)
2) your select statement produces two or more rows with the same primary key.

There's no other scenario I can think of.

You could try that INSERT and put it in a temp table instead. Then check the temp table for duplicates.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources