Primary key constraint recurring 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 2008 Forums

Transact-SQL (2008)

Primary key constraint recurring problem

Author

Topic

texassynergy
Starting Member

26 Posts

Posted - 2010-08-13 : 10:42:59

Posted this yesterday. Received some help that initially worked. Now the problem is back. Don't understand what SQL could be doing to cause this.

I receive a primary key constraint violation when running the following insert:

INSERT INTO MLFML (BOOK_QTY, CREATED_BY, DATE_CREATED, DATE_LAST_ACT, DATE_LAST_ISSUE, DATE_LAST_PHY, DATE_LAST_PRINT, DATE_LAST_RECPT, MLFML_USER_1, 
MLFML_USER_2, MLFML_USER_3, MLFML_USER_4, ON_HAND_QTY, OPERATOR_ID, PART_ID, PHYSICAL_QTY, STOCK_LOCATION, STORES_CODE, DATE_LAST_UPDT, TIME_LAST_UPDT,  DELETE_FLAG) 

SELECT 0, 'SYS.DM', GETDATE(), @TODAY, NULL, @TODAY, NULL, @TODAY, 0, 0, '', '', PI.QUANTITY, 'SYS.DM', PI.PART_ID, 0, PI.TO_LOCATION, PI.STORES_CODE, 
convert(char(11),getdate(),101), convert(char(5),getdate(),108), 'N'

FROM PISTOCKTRANSFER PI
WHERE NOT EXISTS (SELECT 1 FROM MLFML WHERE (STORES_CODE = PI.STORES_CODE) AND (PART_ID = PI.PART_ID) AND (STOCK_LOCATION = PI.TO_LOCATION))

I use the following to validate what records should be inserting.

SELECT * FROM PISTOCKTRANSFER PI 
WHERE NOT EXISTS(SELECT 1 FROM MLFML  WHERE (STORES_CODE = PI.STORES_CODE) AND 
(PART_ID = PI.PART_ID) AND (STOCK_LOCATION = PI.TO_LOCATION))

There are 21 records that should be loaded. Each record is unique in the PISTOCKTRANSFER table, based off of the primary key in the MLFML table which is STORES_CODE, PART_ID and STOCK_LOCATION. The above SELECT statement returns back 21 records just as I would expect. But for some reason they won't load using the INSERT above. There is an Identity key, (not part of the Primary Key), but that should get updated automatically.

Any help would be greatly appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-13 : 12:06:32

If you run these two queries you get the same row count?

SELECT * FROM PISTOCKTRANSFER PI 
WHERE NOT EXISTS(SELECT 1 FROM MLFML  WHERE (STORES_CODE = PI.STORES_CODE) AND 
(PART_ID = PI.PART_ID) AND (STOCK_LOCATION = PI.TO_LOCATION))

SELECT DISTINCT PI.STORES_CODE, PI.PART_ID, PI.TO_LOCATION FROM PISTOCKTRANSFER PI 
WHERE NOT EXISTS(SELECT 1 FROM MLFML  WHERE (STORES_CODE = PI.STORES_CODE) AND 
(PART_ID = PI.PART_ID) AND (STOCK_LOCATION = PI.TO_LOCATION))

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-13 : 12:07:46

Are you using NOLOCKs or READ UNCOMMITTED isolation level?

texassynergy
Starting Member

26 Posts

Posted - 2010-08-13 : 12:29:56

Thanks Lamprey, you hit it on the head. I have been working on so many queries, I lost my place where I was at. There are duplicate records for this table which is why I was having this problem. We have ran 5 tests since yesterday. Trying to meet a deadline for this weekend. The last test had duplicate records for this table.

I know what the problem is and my fix is to get a good sample of test data. I appreciate the help. Sometimes it takes an outside view to see your own problem. Once again, I appreciate the help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-13 : 14:00:27

No worries. I think we've all been there! :)

Good luck on the project.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources