|
|
Can't DROP CONSTRAINT DF_ on a bit data type? |
|
Author |
Message |
estesrocketeer
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
>>>>
>>>>
>>>>
>>
>>
>
>
|
|
|
|
|
|
|