drop the Default constraints in MSSQL code.

when there is a need to drop the default constraint on a column in MSSQL code, please check whether you have created a name for Default constraint.

please not that i had not created a name for default constraint, instead i just created a value for that constraint. so dropping that
was a tedious task. so came across couple of websites/blogs that suggested that MSSQL server itself will create a name for default constraint id not created manually.


use [tsql_Prog_testing]
 go
 select * from sys.sysobjects where xtype='U';

-- create a new table CUSTOMERS
create table customers

(
ID int not null,
Name varchar(20)not null,
Age int not null,
address varchar(25),
salary decimal(18,2),
primary key (ID)
)

— make salary column not to have null values. i.e alter the table

ALTER TABLE CUSTOMERS alter column salary decimal(18,2) not null;

— add default value to Salary coulmn

alter table customers add default 5000.00 for Salary;

— drop the DEFAULT constraint
when there is a need to drop the default constraint on a column in MSSQL code, please check whether you have created a name for Default constraint. please not that i had not created a name for default constraint, instead i just created a value for that constarint. so dropping that was a tedious task. so came accross couple of websites/blogs that suggested that SQL server itself will create a name for default constraint id not created manually.
In general, when you don’t name a default constraint, SQL Server will assign one.
you can check by executing the command
ref – https://technet.microsoft.com/en-us/library/aa175912(v=sql.80).aspx


-- sp_help customers;

–we can create a default constraint name for column as shown below
http://blog.sqlauthority.com/2008/05/31/sql-server-create-default-constraint-over-table-column/

so finally checked the table description using

 sp_help customers 

which gave me the details of default column name as – [DF__customers__salar__014935CB]

PFA

drop_Default_constraint_MSSQL

so use the drop constraint as shown below
— drop the DEFAULT constraint

 alter table customer drop constraint [DF__customers__salar__014935CB]; 

-thanks

sql010

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s