Can't DROP CONSTRAINT DF_ on a bit data type?  
Author Message
estesrocketeer





PostPosted: Tue Jan 18 12:13:04 CST 2005 Top

SQL Server Developer >> Can't DROP CONSTRAINT DF_ on a bit data type?

I can't seem to drop a DEFAULT CONSTRAINT on a bit data type in SQL Server
2000. I try the following two statements but neither works:

EXEC SP_UNBINDEFAULT 'MyTable.MyField'
or

ALTER TABLE MyTable DROP CONSTRAINT DF_MyTable_MyField

SQL Server234  
 
 
AlejandroMesa





PostPosted: Tue Jan 18 12:13:04 CST 2005 Top

SQL Server Developer >> Can't DROP CONSTRAINT DF_ on a bit data type? Could you post the DDL for this table?



AMB



> I can't seem to drop a DEFAULT CONSTRAINT on a bit data type in SQL Server
> 2000. I try the following two statements but neither works:
>
> EXEC SP_UNBINDEFAULT 'MyTable.MyField'
> or
>
> ALTER TABLE MyTable DROP CONSTRAINT DF_MyTable_MyField
>
>
>
 
 
Rob





PostPosted: Tue Jan 18 12:33:52 CST 2005 Top

SQL Server Developer >> Can't DROP CONSTRAINT DF_ on a bit data type? I found the answer with Microsoft MSDN:



(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'Customers'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.Customers') AND
name = 'cust_name'))

+ @defname


Customers = MyTable
Cust_Name = MyField

Does this seem like a lot of work for such a simple task -- hmmmm.... 1
step forward, 2 steps back.



> Could you post the DDL for this table?
>
>
>
> AMB
>

>
>> I can't seem to drop a DEFAULT CONSTRAINT on a bit data type in SQL
>> Server
>> 2000. I try the following two statements but neither works:
>>
>> EXEC SP_UNBINDEFAULT 'MyTable.MyField'
>> or
>>
>> ALTER TABLE MyTable DROP CONSTRAINT DF_MyTable_MyField
>>
>>
>>


 
 
Tibor





PostPosted: Tue Jan 18 13:00:03 CST 2005 Top

SQL Server Developer >> Can't DROP CONSTRAINT DF_ on a bit data type? Can you post a reproduction script? Below work just as expected on my machine:

CREATE TABLE t(c1 bit)
GO
ALTER TABLE t ADD CONSTRAINT t_df DEFAULT 1 FOR c1
GO
ALTER TABLE t DROP CONSTRAINT t_df
GO
--DROP TABLE t



--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/




>I can't seem to drop a DEFAULT CONSTRAINT on a bit data type in SQL Server 2000. I try the
>following two statements but neither works:
>
> EXEC SP_UNBINDEFAULT 'MyTable.MyField'
> or
>
> ALTER TABLE MyTable DROP CONSTRAINT DF_MyTable_MyField
>
>


 
 
Aaron





PostPosted: Tue Jan 18 13:50:45 CST 2005 Top

SQL Server Developer >> Can't DROP CONSTRAINT DF_ on a bit data type? Can you provide a repro (CREATE TABLE script, including constraint
definition) and explain what "neither works" mean... e.g. if you get an
error message, what is it?

--
http://www.aspfaq.com/
(Reverse address to reply.)






> I can't seem to drop a DEFAULT CONSTRAINT on a bit data type in SQL Server
> 2000. I try the following two statements but neither works:
>
> EXEC SP_UNBINDEFAULT 'MyTable.MyField'
> or
>
> ALTER TABLE MyTable DROP CONSTRAINT DF_MyTable_MyField
>
>


 
 
Louis





PostPosted: Tue Jan 18 14:03:08 CST 2005 Top

SQL Server Developer >> Can't DROP CONSTRAINT DF_ on a bit data type? What this script does is get the name of the default for your column.
Apparently you did not have the name correct in your statement:

ALTER TABLE MyTable DROP CONSTRAINT DF_MyTable_MyField

If you will notice, this is the statement that you actually execute. So I
don't know what this meant:

> Does this seem like a lot of work for such a simple task -- hmmmm.... 1
> step forward, 2 steps back.



--
----------------------------------------------------------------------------

SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)



>I found the answer with Microsoft MSDN:
>


> (SELECT name
> FROM sysobjects so JOIN sysconstraints sc
> ON so.id = sc.constid
> WHERE object_name(so.parent_obj) = 'Customers'
> AND so.xtype = 'D'
> AND sc.colid =
> (SELECT colid FROM syscolumns
> WHERE id = object_id('dbo.Customers') AND
> name = 'cust_name'))



>
> Customers = MyTable
> Cust_Name = MyField
>
> Does this seem like a lot of work for such a simple task -- hmmmm.... 1
> step forward, 2 steps back.
>


>> Could you post the DDL for this table?
>>
>>
>>
>> AMB
>>

>>
>>> I can't seem to drop a DEFAULT CONSTRAINT on a bit data type in SQL
>>> Server
>>> 2000. I try the following two statements but neither works:
>>>
>>> EXEC SP_UNBINDEFAULT 'MyTable.MyField'
>>> or
>>>
>>> ALTER TABLE MyTable DROP CONSTRAINT DF_MyTable_MyField
>>>
>>>
>>>
>
>


 
 
Rob





PostPosted: Tue Jan 18 15:25:08 CST 2005 Top

SQL Server Developer >> Can't DROP CONSTRAINT DF_ on a bit data type? It means, I made a mistake, I thought I had provided a valid DF_ name, but I
realize now that SQL provided the DF_ name which was not the same as what I
tried to DROP.




> What this script does is get the name of the default for your column.
> Apparently you did not have the name correct in your statement:
>
> ALTER TABLE MyTable DROP CONSTRAINT DF_MyTable_MyField
>
> If you will notice, this is the statement that you actually execute. So I
> don't know what this meant:
>
>> Does this seem like a lot of work for such a simple task -- hmmmm.... 1
>> step forward, 2 steps back.
>
>
>
> --
> ----------------------------------------------------------------------------

> SQL Server MVP
>
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
>


>>I found the answer with Microsoft MSDN:
>>


>> (SELECT name
>> FROM sysobjects so JOIN sysconstraints sc
>> ON so.id = sc.constid
>> WHERE object_name(so.parent_obj) = 'Customers'
>> AND so.xtype = 'D'
>> AND sc.colid =
>> (SELECT colid FROM syscolumns
>> WHERE id = object_id('dbo.Customers') AND
>> name = 'cust_name'))



>>
>> Customers = MyTable
>> Cust_Name = MyField
>>
>> Does this seem like a lot of work for such a simple task -- hmmmm.... 1
>> step forward, 2 steps back.
>>


>>> Could you post the DDL for this table?
>>>
>>>
>>>
>>> AMB
>>>

>>>
>>>> I can't seem to drop a DEFAULT CONSTRAINT on a bit data type in SQL
>>>> Server
>>>> 2000. I try the following two statements but neither works:
>>>>
>>>> EXEC SP_UNBINDEFAULT 'MyTable.MyField'
>>>> or
>>>>
>>>> ALTER TABLE MyTable DROP CONSTRAINT DF_MyTable_MyField
>>>>
>>>>
>>>>
>>
>>
>
>