|
Home > Archive > Microsoft SQL Server forum > August 2005 > change not null to null, default value to empty
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
change not null to null, default value to empty
|
|
| skyloon 2005-08-03, 3:23 am |
| hi,
my structure table in database:
Amount float(53) not null default 0
when i try to run his script:
alter table ABC alter column Amount float(53) null
it can only set the Amount to allow null, but can't set the default
value to empty.
anyone know how to set the field to allow null and default set to
empty, no value.
thanks
| |
| Damien 2005-08-03, 3:23 am |
| skyloon wrote:
> hi,
> my structure table in database:
> Amount float(53) not null default 0
>
> when i try to run his script:
>
> alter table ABC alter column Amount float(53) null
>
> it can only set the Amount to allow null, but can't set the default
> value to empty.
>
> anyone know how to set the field to allow null and default set to
> empty, no value.
>
> thanks
You need to find the name of the default constraint and drop it.
Unfortunately, the default wasn't given a name in the original DDL. If
it's the only default in the table, the following works:
create table Blah (
Amount float(53) not null default 0
)
go
alter table Blah alter column Amount float(53) null
go
declare @DefAmount sysname
select @DefAmount = OBJECT_NAME(ID) from sysobjects where parent_obj =
OBJECT_ID(N'Blah') and OBJECTPROPERTY(ID,N'
IsDefaultCnst') = 1
exec ('alter table Blah drop constraint ' + @DefAmount)
go
Unfortunately, I can't remember how to tell which column a default
constraint applies to. Anyone else?
Damien
| |
| Simon Hayes 2005-08-03, 3:23 am |
| See sysconstraints in Books Online:
select object_name(constid)
from sysconstraints sc
join syscolumns sm
on sc.id = sm.id
and sc.colid = sm.colid
where sc.id = object_id('dbo.MyTable')
and sm.name = 'Amount'
and sc.status | 5 = sc.status
Simon
| |
| skyloon 2005-08-03, 7:23 am |
| alter table ABC alter column Amount float(53) null
ALTER TABLE ABC
DROP CONSTRAINT df_ABC_Amount
with this script, will it cause any problem in the record?
will data lost or corrupt?
| |
| skyloon 2005-08-03, 7:23 am |
|
alter table ABC alter column Amount float(53) null
ALTER TABLE ABC
DROP CONSTRAINT df_ABC_Amount
if run this script, will it cause the data corrupted or data lost?
| |
| Erland Sommarskog 2005-08-03, 8:24 pm |
| skyloon (skyloon@gmail.com) writes:
> alter table ABC alter column Amount float(53) null
> ALTER TABLE ABC
> DROP CONSTRAINT df_ABC_Amount
>
>
> if run this script, will it cause the data corrupted or data lost?
No, that script should run just fine.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
|
|
|
|
|