Error Handling While Creating Constraints

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)

Error Handling While Creating Constraints

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-03-16 : 08:35:01

Hi,

I have some tables with data, now am creating UNIQUE constriant on the table like below:

CREATE TABLE dbo.ct_Test5
(
rtid VARCHAR(10)
)

INSERT INTO ct_Test5 VALUES('111')
INSERT INTO ct_Test5 VALUES('111')

ALTER TABLE ct_Test5 ADD CONSTRAINT ucConstraint UNIQUE(rtid)

I am getting the below error message after terminating the sp execution:

Msg 1505, Level 16, State 1, Line 10
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ct_Test5' and the index name 'ucConstraint'. The duplicate key value is (111).
Msg 1750, Level 16, State 0, Line 10
Could not create constraint. See previous errors.

To avoid sp termination i used the below TRY...CATCH block.

CREATE TABLE #tempErrorMessages
(
errMessage VARCHAR(MAX)
)

BEGIN TRY
ALTER TABLE ct_Test5 ADD CONSTRAINT ucConstraint UNIQUE(rtid)
END TRY
BEGIN CATCH
INSERT
#tempErrorMessages
SELECT error_message()
END CATCH

SELECT * FROM #tempErrorMessages

but in the table #tempErrorMessages only the error message 'Could not create constraint. See previous errors.' is inserting.

but i want to insert the below error message also in the table #tempErrorMessages to maintain the log history how to achieve this

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ct_Test5' and the index name 'ucConstraint'. The duplicate key value is (111).

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-03-16 : 10:13:15

only cure....remove the duplicate records beforehand

select myproblemcol, count(*) from mytable
group by myproblemcol
having count(*) > 1

to identify same

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources