find out which values are throwing a constraint

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

find out which values are throwing a constraint

Author

Topic

MyronCope
Starting Member

46 Posts

Posted - 2008-04-11 : 15:05:08

I am trying to merge records with a stored procedure and I am getting a constraint violation error. Just wondering is there a way of finding out which records are the actual culprit?

This would help greatly in being able to troubleshoot this issue.

The error is this:

2/21/2008 12:40:39 PM :: Violation of UNIQUE KEY constraint 'SK_SomeConstraint'. Cannot insert duplicate key in object 'tblTable'.
The statement has been terminated.

The constraint is a group of clustered indexes that must be unique:

GO
/****** Object: Index [SK_SomeConstraint] Script Date: 04/11/2008 14:56:17 ******/
ALTER TABLE [dbo].[tblTable] ADD CONSTRAINT [SK_SomeConstraints] UNIQUE NONCLUSTERED
(
[IDa] ASC,
[IDb] ASC,
[IDc] ASC,
[someField] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 15:15:44

Just do a SELECT [IDa],[IDb],[IDc],[someField] FROM (your source data query) GROUP BY [IDa],[IDb],[IDc],[someField] HAVING COUNT(*) >1
to find out combination that repeats.

MyronCope
Starting Member

46 Posts

Posted - 2008-04-11 : 15:22:02

Thanks for the response, but I am not able to do that query because I think you assume the insert is happening, but the problem is because of the constraint the insert is not happening so is there a way of finding out which record or records is causing the constraint error to occur?

I need something similar to what .net uses for exception handling to find out more information.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 15:25:49

Nope. I never assumed so as i know insert never happens when constraint is violated. What i told was to take the query you use to insert to table from procedure and apply my quuery on it

SELECT [IDa],[IDb],[IDc],[someField] FROM (your query from procedure used to insert value) GROUP BY [IDa],[IDb],[IDc],[someField] HAVING COUNT(*) >1
If you are still unclear can you post stored procedure whose execution causes this error.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-11 : 16:00:48

[code]SELECT
A.IDa, A.IDb, A.IDc, A.SomeField
FROM
MySourceTable AS A
INNER JOIN
MyDestinationTable AS B
ON A.IDa = B.IDa
AND A.IDb = B.IDb
AND A.IDc = B.IDc
AND A.SomeField = B.SomeField

--OR

SELECT
IDa, IDb, IDc, SomeField
FROM
MySourceTable AS A
WHERE
EXISTS
(
SELECT * FROM MyDestinationTable AS B
WHERE A.IDa = B.IDa
AND A.IDb = B.IDb
AND A.IDc = B.IDc
AND A.SomeField = B.SomeField
)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources