Default value for columns

Question

I have a table in which there is a column "CompanyID int not null", and its default value is 10. Now I want to write a query that will change this default value to 1. How can I do this?

Any help would be appreciated. I am using SQL Server 2000.

Answer 1

I think the best you can do is reset the constraint and create it again:

alter table dbo.yourTable drop constraint default_value_name_constraint go alter table dbo.yourTable add constraint default_value_name_constraint default YourValue for ColumnName go 

Answer 2

First find the name of the “constraint” in the field that is used to set the default value. You can do this by running this query:

 EXEC sp_helpconstraint 'MyTable' 

Then you can simply remove and add the constraint again.

 ALTER TABLE dbo.MyTable DROP CONSTRAINT def_MyTable_CompanyID GO ALTER TABLE dbo.MyTable ADD CONSTRAINT def_MyTable_CompanyID DEFAULT (1) FOR CompanyID GO 

Answer 3

 ALTER TABLE tablename ALTER COLUMN CompanyID SET DEFAULT 1; 

Answer 4

 ALTER TABLE tablename ALTER COLUMN CompanyID SET DEFAULT Pending; 

I tried this in mysql and gave an error near Pending

Then i tried

  ALTER TABLE tablename ALTER COLUMN CompanyID SET DEFAULT 'Pending'; 

which worked fine.