I hav a table with a column wich has a default constraint. I want to
remove this constraint and to add an other one whitout to delete the column.
It is posible?
Thanks for help!Sure...
ALTER TABLE YourTable
DROP CONSTRAINT DefaultConstraintName
Then...
ALTER TABLE YourTable
ADD CONSTRAINT DefaultConstraintName DEFAULT (default_value) FOR your_column
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:2724888C-8E36-4D03-93F0-06ADCEEC711C@.microsoft.com...
> I hav a table with a column wich has a default constraint. I want to
> remove this constraint and to add an other one whitout to delete the
column.
> It is posible?
> Thanks for help!
>|||Mihaly,
Use alter table to drop the constraint and add the new one.
Example:
use northwind
go
create table t (
colA varchar(25) default ('sql server 2000')
)
go
insert into t default values
go
select * from t
go
declare @.sql nvarchar(4000)
declare @.cnstname sysname
select
@.cnstname = [name]
from
sysobjects as so
where
xtype = 'D'
and parent_obj = object_id('dbo.t')
and col_name(parent_obj, info) = 'colA'
if @.cnstname is not null
begin
set @.sql = N'alter table dbo.t drop constraint ' + @.cnstname
execute sp_executesql @.sql
end
go
alter table t
add constraint df_sql200 default ('sql server 200') for colA
go
insert into t default values
go
select * from t
go
alter table dbo.t drop constraint df_sql200
go
alter table t
add constraint df_sql2005 default ('sql server 2005') for colA
go
insert into t default values
go
select * from t
go
drop table t
go
AMB
"Mihaly" wrote:
> I hav a table with a column wich has a default constraint. I want to
> remove this constraint and to add an other one whitout to delete the colum
n.
> It is posible?
> Thanks for help!
>|||No. Not for you. And whose default is that (ba boom ching!)
Yes, it is possible:
create table test
(
test int,
defaulted int constraint default_test_defaulted default (10)
)
go
insert into test (test) values (1)
go
select * from test
go
alter table test drop constraint default_test_defaulted
--note, I didn't specify a constraint name
alter table test add default 20 for defaulted
--best practice would have included a name, but I wanted you to see what
would happen
--this is the right way:
--alter table test add constraint default_test_defaulted default 20 for
defaulted
--To find the name if you didn't give it a nice name
select * from sysobjects where xtype = 'D' and parent_obj =
object_id('test')
alter table test drop constraint DF__test__defaulted__79A81403
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:2724888C-8E36-4D03-93F0-06ADCEEC711C@.microsoft.com...
> I hav a table with a column wich has a default constraint. I want to
> remove this constraint and to add an other one whitout to delete the
> column.
> It is posible?
> Thanks for help!
>
