Tuesday, February 14, 2012

changing default values

How can i change the default value of a column? I already have a column
named DateOfRental but I want to alter it so that it has default value
getdate()

Thanks
David

--
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious"David Wright" <David@.prodigiousuk.com> wrote in message
news:btjtsg$dbi$1@.news8.svr.pol.co.uk...
> How can i change the default value of a column? I already have a column
> named DateOfRental but I want to alter it so that it has default value
> getdate()
> Thanks
> David
> --
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious

1. Use sp_help to get the current name of the constraint

exec sp_help MyTable

2. Drop the constraint

alter table MyTable drop constraint <Constraint name goes here
3. Add a new constraint

alter table t1 add constraint DFT_DateOfRental default getdate() for
DateOfRental

Simon|||Will try that, thanks Simon

David

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3ffda2ef$1_1@.news.bluewin.ch...
> "David Wright" <David@.prodigiousuk.com> wrote in message
> news:btjtsg$dbi$1@.news8.svr.pol.co.uk...
> > How can i change the default value of a column? I already have a column
> > named DateOfRental but I want to alter it so that it has default value
> > getdate()
> > Thanks
> > David
> > --
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious
> 1. Use sp_help to get the current name of the constraint
> exec sp_help MyTable
> 2. Drop the constraint
> alter table MyTable drop constraint <Constraint name goes here>
> 3. Add a new constraint
> alter table t1 add constraint DFT_DateOfRental default getdate() for
> DateOfRental
> Simon|||How can i drop a constraint i never made though? its not in the list when i
try the SP_help :(

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3ffda2ef$1_1@.news.bluewin.ch...
> "David Wright" <David@.prodigiousuk.com> wrote in message
> news:btjtsg$dbi$1@.news8.svr.pol.co.uk...
> > How can i change the default value of a column? I already have a column
> > named DateOfRental but I want to alter it so that it has default value
> > getdate()
> > Thanks
> > David
> > --
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious
> 1. Use sp_help to get the current name of the constraint
> exec sp_help MyTable
> 2. Drop the constraint
> alter table MyTable drop constraint <Constraint name goes here>
> 3. Add a new constraint
> alter table t1 add constraint DFT_DateOfRental default getdate() for
> DateOfRental
> Simon|||David Wright (David@.prodigiousuk.com) writes:
> How can i drop a constraint i never made though? its not in the list
> when i try the SP_help :(

If there is no constraint, you should not have to drop it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

No comments:

Post a Comment