SQL Error : Column already has a DEFAULT bound to it
January 6, 2013 1 Comment
The Problem
Today I was testing an application that using SQL server 2008 DB. I got an error in my application says that a table is missing in the DB. I decided to generate CREATE SQL script from other DB that has the missing table and execute in this target DB.
While executing the CREATE SQL script I got these errors:
There is already an object named 'SANotify' in the database. Msg xxx, Level xx, State x, Procedure sp_addextendedproperty, Line xx Property cannot be added. Property ... already exists for ... Msg xxx, Level xx, State x, Line x Column already has a DEFAULT bound to it. Msg xxx, Level xx, State x, Line x Could not create constraint. See previous errors.
Troubleshoot and Solution
To trouble shoot this error, I decided first to list all constrains in this table, for that I found this helpful post. In my table, all constrains names starts with DF_SANotify so to get all these constrains I executed the following SQL:
SELECT name,OBJECT_NAME(parent_object_id) AS TableName, OBJECT_NAME(object_id) AS ConstraintName FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT' and name like 'DF_SANotify_%' order by name
and I got this result:
As you can see .. I found that the constrains are really exist because the table SANotify I am trying to create is already exist but renamed to XXSANotify.
So I deleted the XX table, ran the SQL script and it is executed successful. Instead of deleting the XXSANotify I could just rename it back to SANotify.