Alter column's default value

Question

I have a table which has a column 'CompanyID int not null' and its default value is set to 10. Now I want to write a query which will alter this default value to 1. How can can I do it?

Any help will be appreciated. I am using SQL server 2000.

Haplology

Are you saying you wish to set the rows which have been defaulted to 10 to a new value? (In which case you may find it hard to tell the difference between a row that has been defaulted to 10 and a row that has been manually set to 10.) – Salem

There are not entry in the table. I want to change the CompanyID column default value to 1.so that every time i insert data in the table , it should automatically insert 1 in the CompanyID column.Earlier the default value is set through constraint. – Haplology

Answer 1

I think the best you can do is drop 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

Goodhumored

Answer 2

First, find out the name of the 'constraint' on the field which is used to set the default. You can do this by running this query:

EXEC sp_helpconstraint 'MyTable'

Then, you can just drop and re-add the constraint.

ALTER TABLE dbo.MyTable
DROP CONSTRAINT def_MyTable_CompanyID
GO

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

Intussusception

Answer 3

ALTER TABLE tablename ALTER COLUMN CompanyID SET DEFAULT 1; 

Flu

I have tried this, it is not working.The error is 'Incorrect syntax near the keyword 'SET'.' – Haplology

Answer 4

ALTER TABLE tablename ALTER COLUMN CompanyID SET DEFAULT Pending;

I have tried this in mysql and it was giving error near Pending

Then I tried

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

which worked fine.

Naval