Thursday, December 4, 2008

When constraint remains in system table even the table is no longer exists

Ok, I'm talking about temporary tables and yes,it could happen to you even you think that the temporary table is no longer exists. Consider the below script where stored procedure inserts the data with wrong type which caused the error(245conversion error). As you propably know we would never name the constraints for local temporary tables (as opposite to permanent tables), because if we run the following statement from two connections we get something like that.
/*
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'PK_tmp1' in the database
*/


USE master
GO
ALTER PROCEDURE spSample
AS
CREATE TABLE #tmp(
col int --PRIMARY KEY NONCLUSTERED--,
CONSTRAINT PK_tmp1 PRIMARY KEY NONCLUSTERED
(
col
)
)
BEGIN TRY
INSERT INTO #tmp SELECT 'A'
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()
END CATCH

--Usage
USE master
EXEC spSample


USE tempdb
SELECT * FROM sys.objects
WHERE OBJECT_NAME(OBJECT_ID)like '%#tmp%'

Try add DROP TABLE #tmp in BEGIN CATCH ..block and then SQL Server deletes the constraint as well.

No comments: